I’m new and 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 find the top/bottom 10 within the top/bottom customers.
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 mpivot 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 .
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.
In researching top N - I added a 2016 YOY sales calculated field. Then I chose to use a sets to create the top/bottom 10 for the member location field based on the 2016 YOY $'s.
But first - In order to add a 2016 YOY calculated field I had to add 2 calc fields to get the individual 2015 sales and 2016 sales, is this the proper way to do this?
Next, and desperately, what I really need next is to go another top/bottom level and get the top/bottom 10 of the Mfr Roll up for EACH one of those locations. Not the top/bottom Mfr Roll Up for the overall
sales amount.
Is this possible? Thank you for any help you can offer!
hi Bekki,
So you are really going in at the deep end here in terms of table calculations!...
So the issue we have here is that the VizLoD (Member/ MFR RollUp/HL3] is different from the level at which we want to RANK things (Member/ MFR RollUp), so we need to use another Table Calculation (this time we need to first aggregate the results, which are displayed by Member/ MFR RollUp/HL3 to be calculated at Member/ MFR RollUp...so we use a function called WINDOW_SUM)
So first I created a field (using WINDOW_SUM) to SUM up each HL3 within each Member/MFR RollUp...
[2016 YOY SALES - WINDOW_SUM]
WINDOW_SUM([2016 YOY SALES])
I've brought this in, so you can see how it works, and is set up as follows (notice how this field for SCA returns the total from the previous sheet...think of it a bit like doing a SUMIFS in Excel - in reality its very different but can be a useful analogy to understand the calculation)
So once we have this, we can use this field in a RANK...however we want it to give a single RANK for alll rows associated with MFR RolUp...so we change to use RANK_DENSE
So our new RANKing field becomes
[2016 YoY Sales RANK - on WindowSum]
RANK_DENSE([2016 YOY SALES - WINDOW_SUM])
and this is set up follows. notice now that as we have a Table Calculation (WINDOW_SUM) inside another Table Calculation (RANK_DENSE) we get a drop down so we can set up each part...in this case they are both the same!
Now SIZE is no longer a good idea here...it's really counting partitions, so gets hard to control...so I've gone with a different method....create an ascending RANK. So I create this field
[2016 YoY Sales RANK - on WindowSum - Reverse]
RANK_DENSE([2016 YOY SALES - WINDOW_SUM],'asc')
and set this up as follows
right nearly there!...so now we need to create our filter, which just uses these 2 fields
[Show Top/Bottom N Mfr - PH3]
[2016 YoY Sales RANK - on WindowSum] <= [SELECT N VALUES]
OR
[2016 YoY Sales RANK - on WindowSum - Reverse] <= [SELECT N VALUES]
Now this is slightly different to the YOY CHECK, and is probably better. as we are now using RANK DENSE and not SIZE we are taking the last 10 positions from 80, and not 86 (the last 6 are all zero)
I'll let you digest all this before we tackle nesting it all into a single calculation!
