I’m struggling with analyzing data in tableau. Specifically drilling down to top and bottom 10 data. I was hoping someone could help me in figuring out how I can drill down to low level detail.
I have a specific example of where I need help in the attached workbook.
Looking at “YOY” tab - I’d like to be able to treat this tab as a pivot table. I would like to only see the Top 10 and bottom 10 "Member Locations" based on the YOY $ sales of 2016 over 2015.
Then of those top/bottom member locations – I would like to only see the top/bottom mfr’s roll up for those member locations. That would allow me to isolate which products are driving these
results - Is any of this possible?
The reason I’m in need of this is, not so much for a viz, but more so for a narrative report. I need to pinpoint our good and bad member locations and the products that are driving these results. On the flip side, I
will also need to do this with QOQ results, then MFR roll up vs member roll up, and other scenarios. I'm no longer able to do this in Excel because the data is too large.
Thanks in advance for taking the time to look at this.
Bekki
Hi Bekki,
I have Tableau Desktop 10.3 and can't save to 10.2, so I'll explain with screenshots.
To answer your first question, I think it's a good idea to put 2016 Sales, 2015 Sales, and the YoY dif as their own measure fields. Because they are their own measure fields, you can use them in other calculations. Another option would be to show Sales broken out by Year-as-a-dimension, then use a Table Calculation to find the difference, but you have so much less flexibility to use the YoY Dif in other calculations (and sets) if it is a table calc.
To go to a second level of Top/Bottom N, I would use a rank formula. You can use this to find Top/Bottom in the same table. I'm going to show several different formulas in the table so you can see what they are doing, but then we can combine some of the formulas into one for simplicity. Also, in this example I will hardcode this to be Top/Bottom 3 so we can see the results in the table more easily, but you can of course change the 3 to refer to a parameter.
Rank High to Low: RANK_DENSE([2016 YOY SALES])
Rank Low to High: RANK_DENSE([2016 YOY SALES],'asc')
Top-Bottom Filter uses these two formulas:
IF RANK_DENSE([2016 YOY SALES]) <= 3 THEN 'Top'
ELSEIF RANK_DENSE([2016 YOY SALES],'asc') <= 3 THEN 'Bottom'
ELSE 'Hide'
END
After creating these formulas, convert them all to "Discrete". Place them in the table between Member Location and MFR Rollup.
Right click on each calculation, go to Compute Using > and choose Pane (down). This will make the Rank restart at each Member Location.
So now we have the Top and Bottom 3 separated out in our table:
You can now add this to the filter shelf, and choose only "Top" and "Bottom". When you add it to the filter shelf, you will have to again go and set Compute Using > Pane (down) to get the right results:
Now you can remove the Rank High to Low and Rank Low to High, the Top/Bottom Filter works without it.
However, without the Rank column, your values are no longer sorted descending.
If you like, you can leave one of the rank columns in altogether and it will sort your values and show the rank:
You can create a combined field that will let both Top and Bottom be 1-N Ranked:
Top-Bottom Rank:
IF RANK_DENSE([2016 YOY SALES]) <= 3 THEN RANK_DENSE([2016 YOY SALES])
ELSEIF RANK_DENSE([2016 YOY SALES],'asc') <= 3 THEN RANK_DENSE([2016 YOY SALES],'asc')
END
Or finally, you can maintain the sort from the Rank column by keeping the Rank column in the Table, but hiding it. To do this right click on any value in Top-Bottom Rank, and uncheck "Show Header".
The column still exists in the table so it maintains the sort, but it is hidden from view:
Ok, I hope this gets you started.
Best of luck,
Jennifer
