11 Replies Latest reply on Dec 22, 2014 1:53 PM by Thomas Tegtmeier

# Not getting grand totals for complex table calculations

Hi All,

I'm new to Tableau and this forum, so please forgive my potential misuse of terminology and shoddy workbook example.

I'm having issues getting grand totals on a column of table calculations. I understand why, I've used the ATTR() function, but I am hoping that someone might be able to help me find a better way to do what I've done. I am attaching the workbook because this is a bit hard to explain.

I am calculating a 10 period rolling average based on each hour of each day for each browser in the table. I've done this by creating table calculations for each browser and then rolling them up into one table calculation (Hourly Rolling Average). I then use that rolling average as part of a revenue loss calculation. This seems to work fine in the table, but it will not roll up to a grand total for me. I believe this is because I have used the ATTR() function and it only allows for the return of a singe variable, but I haven't been able to figure out a better way of doing this.

Once I solve that particular issue I need to be able to take the revenue loss calculation and sum/trend it by day, week, month,year, etc.

Thanks in advance for any help!

• ###### 1. Re: Not getting grand totals for complex table calculations

Hi Philip,

There are a few things going on that I could see. All the calculated fields to generate the Rolling Average [Browser] measures are slowing down performance a lot - The Master Table view takes over 30 seconds to display on my computer. One thing to know is that according to Tableau tech support, IF/THEN statements are faster than IIF.

If I understand your requirements correctly, you can calculate the rolling average for each browser & date hour over the past 10 days by using the Advanced... Compute Using settings of a table calculation, rather than having to compute several different rolling averages, then have the other calculations use that. See the Rolling Avg Bounce Rate measure in the Rolling Avg Table Calc worksheet for details, that view takes <5 seconds to update, and has the advantage of not having to add a new measure and revise calculations when new browsers are introduced.

Also, the way you have the view set up it's returning 23K+marks. Though Tableau is perfectly capable of drawing hundreds of thousands of marks in a view, when a text table has so many values it's not that readable. I'm hoping you can find ways to slim down what is displayed and/or find alternative visualizations.

Now to the Grand Totals and why they are not displaying as desired. Tableau computes Grand Totals as a separate calculation using the same formula at a higher level of aggregation. To turn that into plainer English, when you turn on a Grand Total for columns, for the Grand Total Tableau runs the calculation as if there was nothing on the Rows shelf. So, SUM(Sales) becomes the SUM of sales across all the data, AVG(Sales) becomes the average of each sale across the dataset (and not the average of the averages). What's happening in your case is that in the Grand Total row, since there are no dimensions being multiple values are returned to the ATTR() function, so ATTR() returns Null (the *) and the calculations fail. It's not really an issue of table calculations, you can see the Rolling Avg Bounce Rate in the Rolling Avg Table Calc view is returning a value (not the value you'd want, it's a value based on the SUM(Bounce Rate) across the entire data, since there are no fields available for partitioning the WINDOW_AVG).

If you are wanting to customize grand totals, I suggest you read the following blog posts:

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

Those reference a couple of other posts as prerequisites, here's my full list of posts and videos to help learn table calculations:

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

http://community.tableau.com/message/184716#184716 (Joe Mako's intro to Table Calculations from Think Data Thursday)

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://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

http://community.tableau.com/message/139603#139603 (Order of filtering)

Jonathan

2 of 2 people found this helpful
• ###### 2. Re: Not getting grand totals for complex table calculations

Jonathan, thanks so much for your help.

I'll work through this and get back to you.

• ###### 3. Re: Not getting grand totals for complex table calculations

Jonathan,

I've gone through the information/tutorials that you posted. Thanks a ton, it was very helpful. I was able to get this worksheet to run much faster and was able to get rid of all of those IIF and ATTR() statements. However, I've still not been able to get the grand total rows to behave as I would like.

I think that my problem is that I am relying on the [Rolling Average (BR)] table calculation and this is preventing me from creating a custom total for the [Revenue Loss] column. Essentially I want it to be just a sum of all the values in the rows, but I've been unsuccessful because 1) you can't aggregate and aggregate, and 2) I can't create a special grand total formula for revenue loss because the output would be completely different (again because the [Rolling Average (BR)] is meaningless from a total average perspective) than simply adding the sum of the rows.

• ###### 4. Re: Not getting grand totals for complex table calculations

Hi Philip,

I'm sorry, I've got a few other commitments and won't be able to look at it for a few days, I'm checking if I can get someone else to respond sooner.

Jonathan

• ###### 5. Re: Not getting grand totals for complex table calculations

At Jonathan's request, I took a look at your workbook. From what I can tell your data level of detail is the combaination of "Browser" "Date_day" and "Date_hour". For each distinct combination of those fields, only one record exists in your source data.

A quick profile of this data reveals that there are many gaps in your data, not all possible combinations exist, eg there are hours for dates where there is no record for some browsers.

This leads to questions of exactly what your business logic for performing your calculations should be. Just saying you want the average of the past 10 days for each hour-browser combination is not specific enough because every possible hour-date-browser combination does not exist in your data.

Do you have data that fills in these gaps? or do you want pad your data and use zero where there is no data? or do you want to use null when padding for missing? or do you not pad and average the moving 10 existing values, even if they are not consecutive dates?

There are many more questions like these, too many to type out on a forum thread, that I would like to discuss with you to enable me to provide you assistance. You can find my email on my profile page.

• ###### 6. Re: Not getting grand totals for complex table calculations

Jonathan,

That is a great collection of links, but if you have the time, I would like to discuss some your inline comments and what you did in your workbook, there may be some confusion.

• ###### 7. Re: Not getting grand totals for complex table calculations

Here is the final view that performs the calcuations as requested.

Think of this as a preview of Jonathan's upcoming post on Grand Totals Part 3

• ###### 8. Re: Not getting grand totals for complex table calculations

Thanks again for your help last night, Joe.

• ###### 9. Re: Not getting grand totals for complex table calculations

This workbook and discussion has been a great help. Thanks for putting it together.

I did have a related question though. If using one of the measures as a quick filter, say Total Bounce Rate, the filter will correctly exclude and include rows, but the grand total value will not be affected. Is it possible to update or recalculate the grand total values, so it would reflect the values being included by the quick filter?

I'm not trying to thread hijack, so if my question is deemed too far from the original topic, just let me know and I can post it on it's own.

• ###### 10. Re: Not getting grand totals for complex table calculations

Yes, you can perform filter in the formula as well as on the filter shelf.

You would need to adjust all formulas to effectively filter, and that complexity is beyond a forum comment.

Please get in touch if you would like more detail.

• ###### 11. Re: Not getting grand totals for complex table calculations

Jeremiah, did you get the answer? I have the same trouble: "filter will correctly exclude and include rows, but the grand total value will not be affected. "

Im getting crazy with this, can anyone help me?

Thanks!