4 Replies Latest reply on Aug 21, 2012 5:55 PM by William Weaver

# How do I create an average weekday count for a measure with unique values?

I need help identifying a way to calculate the average number of cancelled trips per weekday. Since my measure, TripID, is a unique 8-digit number, I cannot just use the built-in Average calculation option because Tableau wants to base the average on the sum up the 8-digit number associated with every TripID. My next thought was to use something like AVG(COUNT(TripID)) but Tableau rejects the calculation since I am trying to aggregate an aggregrate...whatever that means. So, after an unsuccessful search of the forum and the knowledge base, I was hoping someone could help point me in the right direction.

Thanks --- William

• ###### 1. Re: How do I create an average weekday count for a measure with unique values?

Hi William,

In your data, TripID really isn't a measure, it's a discrete dimension with a number data type. By default when Tableau loads the data, it assigns numeric values as continuous measures, so that's why you're seeing TripID (and all the other ID fields) in the Measures section of the Data window. You can put these in the proper location by either clicking+dragging them into the Dimensions section, or by right-clicking and choosing "Convert to Dimension" and then right-clicking again and choosing "Convert to Discrete".

In any case, you can turn most any discrete dimension into a measure by aggregating it, which is what COUNT(), AVG(), MIN(), MAX(), SUM(), etc. do. Tableau won't allow you to use an aggregate function on an aggregate function, that's where Tableau's table calculation functions such as WINDOW_AVG(), WINDOW_MAX(), etc. come in. They are bits of magic that let you perform all sorts of complex aggregations and sorts across the data.

It seems like you're not that familiar with aggregations and table calculations, if you haven't already I suggest you read the following:

http://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/

http://www.tableausoftware.com/table-calculations (and follow all the links here, like the Top 10 Table Calculations)

http://www.thedatastudio.co.uk/blog/the-data-studio-blog/groundwork-for-custom-table-calculations

http://www.tableausoftware.com/support/knowledge-base/table-calculations

http://community.tableau.com/message/139603#139603

http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

In order to generate the average trips per weekday, we need to have each day in the view to compute the number of trips for that day, and the weekday to partition the days. We can't just use the date and the convenience weekday of date, because that makes Tableau do some padding of rows that we don't want, so there's an extra step of generating a calculated field for the weekday of date, that's using the formula DATENAME('weekday',[Order Date]) in the attached.

The formula for the average is:

IF FIRST()==0 THEN

WINDOW_AVG(COUNT([Order ID]), 0, IIF(FIRST()==0,LAST(),0))

END

And it has the Compute Using set to Order Date. The IF and IIF statements are used to improve performance and prevent overlapping text from being returned.

Finally, there's a second table calculation with the formula FIRST()==0 on the Filter shelf, with it's Compute Using also set to Order Date. This further reduces the number of marks Tableau is trying to draw in the view.

Cheers,

Jonathan

2 of 2 people found this helpful
• ###### 2. Re: How do I create an average weekday count for a measure with unique values?

Thanks for the weblinks. Yes, it is pretty obvious that I need to improve my understanding of table calculations since almost every question I have posted over the last couple of months involved a custom calculation solution.

And a very big thanks for the proposed solution. I am planning to give it a try later this afternoon. I will let you know if I have any problems.

In the meantime, I did have a couple of follow up questions.

First, when you talk about "the number of marks" are referring to the double counting of data? If so, would it be accurate to characterize the "first filter" as a means to eliminate double counting from the count?

Next, I was concerned about your reference to "padding of rows". Could you elaborate on what you mean? Will this issue occur every time I use the built-in Weekday calculation?

Thanks --- William

• ###### 3. Re: How do I create an average weekday count for a measure with unique values?

Double-counting of data and number of marks are related but different. Data is double-counted whenever the view is being constructed at a coarser level of detail than the underlying data. We can get rid of that by adding more dimensions to the overall level of detail (by adding them to the Rows or Columns Shelves, or a shelf on the Marks Card), or using an aggregation that effectively removes the duplication like ATTR(), MIN(), or MAX(), or sometimes a table calculation.

If you've ever seen Tableau draw overlapping text in a text table, or sometimes overlapping marks in a view, this is happening when the dimensions chosen for the given cell in the view are directing Tableau to return multiple rows of data to the view and the aggregation is such that Tableau is attempting to draw multiple marks, whether they are text fields, circles, lines, etc.

Tableau displays the number of marks it is attempting to draw on the worksheet in the lower left corner of the window:

To speed up performance, it's best to a) do as many calculations as possible within the data source, b) return as little data as possible from the data source to Tableau, c) have Tableau do as few calculations as possible, and d) have Tableau draw as few marks as possible. The IF and IIF optimizations are about doing c) and d), the "first filter" is d). If you take that "first filter" off the Filters Shelf, you will see the number of marks jump from 7 (one for each weekday) to some much larger number.

Now to your padding question. The good news is that this is not about the Weekday calculation, and really for your case it isn't a padding issue, but a partitioning issue - I got them temporarily confused. In order to have the table calculation address on the Date and partition by the Weekday, we have to create a calculated field because Tableau doesn't give access to the Weekday as an option for partitioning.

The padding issue is something that I'll occasionally run into when I'm building a view, and I had earlier when creating yours. In certain situations with table calculations involving dates, Tableau will pad out the data for every date in the view. This is behavior introduced in version 7 to support displaying time series when there are gaps in the underlying data. You'll immediately notice when this happens because when you set a custom Compute Using, your table calculation that took less than a second to run will suddenly take 40 seconds to several minutes (if not crash your machine if the data set is large enough). The workaround is to create a calculated field for the date in question, and maybe even make it a string. I probably got some parts of this explanation wrong, it's an area of Tableau that I'm still exploring and learning.

Jonathan

• ###### 4. Re: How do I create an average weekday count for a measure with unique values?

Ok. I have definitely seen overlapping text situations but I've always been able to resolve those instances by using the Show Me box to switch to a text table to resolve the issue. Now I'm worried that the table format switch doesn't really resolve the underlying issue. Am I needlessly worrying?

Also, it appears that my average calculation request is actually a little more complicated than I first realized. While your custom calculation does provide the average trip count for each weekday, what I really need is to average a subset of the trip count, the cancelled and no show trips. My best guess, at this point, is that I need to add a WHERE clause to your formula. Is this assumption correct?

I have attached a packaged workbook in case you have time to take a quick look.

Thank --- William