Apologies if this has been asked before - I searched through the forums pretty extensively but haven't found a solution yet.
Using Superstore data as a stand-in, my goal is to be able to have a dynamic top N number of product sub-categories that match results for both a point-in-time/current year as well as year-over-year calculation. I've figured out dynamic top N and the year-over-year calculations; the issue is that the top 5 (for example) that are included in the point-in-time pie chart don't always match the top 5 that are included in the year-over-year calculations. There are cases where product categories may have orders in December 2014, but not December 2015.
For example, in the packaged workbook I've attached, if you select December 2015 and adjust the number of sub-categories to display to 13, copiers will show up in the pie chart as part of the top 13, but not in the year-over-year calculations. It's hard to replicate with this stand-in data, but in the project I'm working on there are cases where copiers would not show up in the pie (because they have 0 quantity for 2015) but do show up in the YOY calculations (because they were in the top 5 in 2014).
Is there a way to get the top N group that is returned to only show the top N for a current selected year, and have that same group of sub-categories match in the YOY calculations?
Some notes on what I've tried so far:
- Using a date quick filter doesn't work on the YOY calculations because then it excludes the required "previous year" component
- I tried using a date component with the index() calculation ("Rank" in the workbook) with the idea that it would only return top N for the most recent year, but couldn't get this to work
- for example: IF ATTR([Order Date]) = [Select Date Parameter] THEN INDEX() END
- also tried: IF MAX([Order Date]) = [Select Date Parameter] THEN INDEX() END
If anyone has a solution for this, I would be forever in your debt.
(Using Tableau 10.0)
Top N Dynamic Example.twbx 1.6 MB