Match Dynamic Top N results for Current Year and YOY Calculations
Kaela Blenkush Feb 20, 2017 2:26 PMApologies 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 standin, my goal is to be able to have a dynamic top N number of product subcategories that match results for both a pointintime/current year as well as yearoveryear calculation. I've figured out dynamic top N and the yearoveryear calculations; the issue is that the top 5 (for example) that are included in the pointintime pie chart don't always match the top 5 that are included in the yearoveryear 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 subcategories to display to 13, copiers will show up in the pie chart as part of the top 13, but not in the yearoveryear calculations. It's hard to replicate with this standin 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 subcategories 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