5 Replies Latest reply on Apr 12, 2013 6:32 AM by Jonathan Drummey

Top N by category, by period with in and out percentages

Hi,

We are trying to utilize the new Tableau Set feature (In/Out) to display the percentages of products sold over a fiscal period-

1. display % of product sales within Top N
2. display % of product sales outside Top N
3. 1 + 2 should be equal to 100% of all product sales within the time period and category

the attached sample is somewhat we are hoping to achieve but the desired results would be (in Sheet 1):

• each of the year will display its Top 3 products and sales and % - right now, in some years, it does not display any Top 3 as the current Top 3 is calculated across all countries, all period, all products
• when we select multiple countries, it will displays the corresponding Top 3
• we would like to use a parameter to enable dynamic selection of Top N by using a parameter

thanks for any advice.

Cheers,

Rick

• 1. Re: Top N by category, by period with in and out percentages

You're going to need to create a combined field of all the dimension(s) you want to do the Top N for and then build your set off that. So if you want the Top N to be based on product, country, and year, then that's what you'll use.

• 2. Re: Top N by category, by period with in and out percentages

Thanks for the lead. I made the changes (see Sheet 2) and it is getting closer to what I am expecting. The outstanding portions are:

1. how do aggregate the individual % of the Top N, I used the default subtotal and while the total "Sales" is what I am expecting the "Percent of Total" is not as expected. As an example (see attached picture), the red circle should be 38.19%+29.43%+20.01%=87.24%.
2. in combining the fields "Country/Region", "YR(Order Date)" and "Item", I utilized the year of the order date. How could I carry something similar but need to do it in fiscal year (starting from July)? I duplicated a Order Date and set the Fiscal to start from July - Order Date (FY). If I try to create a calculated field (datename('year',[Order Date (FY)]), the result seems to be same as (datename('year',[Order Date]).

Cheers,
Rick

• 3. Re: Top N by category, by period with in and out percentages

Hi Rick,

The fiscal date that you set it only for display, not for calculated fields (unfortunately). So you're going to need to build your own field to set the year, such as: IF MONTH([Date]) >= 7 THEN YEAR([Date])+1 ELSE YEAR([Date]) END.

Jonathan

• 4. Re: Top N by category, by period with in and out percentages

Hi Jonanthan,

thanks for the code for the Fiscal year, it worked. BTW, any recommendation on how to fix issue (1), to aggregate the percentages correctly?

cheers,

Rick

• 5. Re: Top N by category, by period with in and out percentages

Hi Rick,

This is complicated and I'm not 100% sure can be done. Tableau's grand totals are computed as a separate computation of the given measure (% of total in this case) at a different level of aggregation, *not* an aggregation of what is displayed in the view, which is what you're asking for. This will return the same results in many cases, but not all. There's an Idea to have Tableau do what you want, btw:  http://community.tableau.com/ideas/1232.

I did a series of posts on how to customize totals, starting with http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/. However, what you're wanting goes beyond that in a couple of ways:

- The goal is to aggregate the % of total table calc in a different way in the subtotal row. This requires a technique that I've only done a few times (and haven't seen anyone else do it) and have yet to write up, I'm still learning the ins and outs of it and have yet to apply it to subtotals, which add a level of complexity.

- The % of total is filtered by a table calc. What's in the grand total and subtotal rows are a separate computation, that is performed in parallel with table calc filters. In other words, grand total and subtotal rows don't aren't affected by table calc filters. That means that we have to make all the table calcs work inside the subtotal rows and effectively implement the filters there. To do that (which I imagine can be done, but I'm not sure) requires increasing the level of detail in the view to have the necessarily granularity within the computation that happens within the subtotal row, and given that we're using a % of total table calc means that that Compute Using (which is already a bit complicated) will need very particular attention so that it continues to work in the regular rows.

Customizing totals of table calculations is like trying to do watch repair in the dark. We can't see the ins and outs of what Tableau is generating for the totals the way we can in a regular crosstab, we have to imagine it and that makes it quite a bit more difficult. Right now I'd say customizing subtotals of table calculations with advanced compute using is like trying to do watch repair in the dark while wearing rubber gloves. I can look at this, but I can't promise success and given my schedule would take a few days (at least).

Jonathan