1 of 1 people found this helpful
I came across a similar problem recently and have been helped along by Joe Mako. I figure it's time I start paying it forward, so, here we go.
I've attached the Super stores workbook to this message with a rank selector showing the top N number of Categories for each City. To get the ranking to work properly--I don't think this can be done by sorting on sales by category and using Tableau's built in top/bot N feature--I created a calculated field that is:
if isnull(lookup(sum([Sales]),0)) then PREVIOUS_VALUE(0) else PREVIOUS_VALUE(0 )+1 end
As I understand it, the calculation above sets the first category for each City/Category pair to 0 and then adds 1 to that value for every record that isn't the first (null) one.
Then I edited that calculated field by entering the Advanced Table Calculation menu as seen below:
After setting the compute using to City and Category (the order along doesn't seem to impact anything for this instance), I set the table calc to reset every City. That gets the ranking to start over each time the city iterates.
Then added that new field, named, Sales by City Rank, in between the pills for City and Category. I made the new field discrete so it displays nicely. If Tableau sees is as continuous it wants to make a bar chart.
Next, to select the Top N, I made a parameter named, Top N Selector and added it to the Viz (right click on the parameter and click, 'add to sheet') and made another calculated field named Top N for Sales with this formula: [Sales by City Rank]<=[Top N Selector] and set it to 'True'. Now, when the Top N. Selector is set to something, the filter automatically filters out any rank that's outside of the selector.
I hope this helps you along your way with your challenge. I think you can use the logic that I applied to category and city to your stores data set and use action filters to tie both your visuals together.
--John C. Munoz
Your answer was very helpful. I am going to build on your example to achieve the end result I am looking for. I attached a new version of your workbook.
I displayed the Top N Products at the State level. I created a dashboard to show both sheets on one view. The goal was to do some analysis at different levels. Maybe the city's top seller was Technology but their state's top seller is Office Supplies (so the city has an opportunity to sell more Office Supplies).
Again, I really appreciate your help. I will be attending the conference in November. Hope to see you there!
Glad I could help Ian. I will be there too. Hope we get the chance to meet up. Follow me on Twitter @johncmunoz and I'll DM you when I'm there.