3 Replies Latest reply on Oct 29, 2013 7:50 AM by Jonathan Drummey

How to have different scopes for a table calculation within one calculation?

I'm trying to produce a graphic like this:

http://www.nytimes.com/interactive/2009/11/26/us/20091126-search-graphic.html

I've produced an example using the Superstore dataset (attached). The steps I need to go through are:

1. For a given category, calculate the proportion of searches from each country that were for that category (orders for this [category, country] divided by all orders for that country)

2. For a given category, calculate the proportion of searches from all countries that were for that country (total orders for this category divided by total orders for all categories)

3. Calculate the % difference between 1 and 2

I'm currently stuck on calculation 2 because I can't see how to use two different scopes in one Table Calculation. I need to calculate:

(total orders for the category set in the page) / (total orders)

and have that displayed in each row. The value should be 5.09%, as shown in the Grand Total for the column.

Any assistance would be greatly appreciated. I'm guessing there must be a better way of approaching this.

Thanks,

Alan

• 1. Re: How to have different scopes for a table calculation within one calculation?

Alan you almost certainly haven't yet received an answer for this well-asked question because it is tailor-made for Jonathan Drummey and we all know that he will come in tomorrow and teach a Zen Master class on all the issues you are struggling with. In the meantime if you want to read what he has written previously go to his blog: Drawing with Numbers and browse away -- your answer is almost certainly in there, along with a whole lot of other useful stuff.

Cheers,

--Shawn

• 2. Re: How to have different scopes for a table calculation within one calculation?

I don't have time to look at this today, but when you need different scopes you typically have a table calculation for one part of the final calc, then include this in the full calc. Tableau provides you with a drop down to select different partitioning for the different calcs.

1 of 1 people found this helpful
• 3. Re: How to have different scopes for a table calculation within one calculation?

Hi Alan,

See the attached. It's using custom calculated fields that are nested table calculations, when you go to the Edit Table Calculation dialog you will see an extra drop-down at the top for each of the calculations where you can pick the calc to set its addressing and partitioning: Also, looking at the NYTimes viz, it does a final ranking from 1 to N. Because the values for the ranking would be defined by a table calc, in Tableau 8.0 we can't compute that all at once because we can't sort a table calculation partition by another table calculation. The way I've done this in the past is to either do the initial ranking in SQL or to do the ranking in Tableau, then export the results to a new Tableau data source that can do the final ranking. The good news is that Tableau v8.1 which is in beta right now has several RANK functions that can all take table calculations as their expression and give you those results.

Jonathan