
1. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Oct 4, 2017 9:50 AM (in response to Bekki McCormick)hi Bekki,
So yes definitely possible...but does touch on a few advanced concepts in Tableau;
First thing I did was to change your YoY calc to
SUM([2016 SALES])

SUM([2015 SALES])
from
SUM([2016 SALES]  [2015 SALES])
If you look at a bit of the data
Your formula was doing a Row Level calculation and then SUMing up the results. This way it aggregates, to whatever level you are looking at before doing the one minus the other.
So next we need to use the RANK formula, so we can RANK the MFRs within each Member Location. The formula for this is
[2016 YoY Sales RANK]
RANK([2016 YOY SALES])
Now this is a Table Calculation, which means we need to tell Tableau how to compute it (do we want it to RANK everything, Top to Bottom?, or do we want to Rank things within each Member Location?)
As we want the second one, we set it up like this
This means run the RANK over Mrf Roll Up, and restart the RANK every new Member Location
Now to get the Bottom N (there are a a few ways)...I've gone with the SIZE function (another Table Calc)
This is just SIZE()
and just returns the number of marks/rows/things depending how we set it up...The same as before we want the number of MRF Roll Up per Member Location
Once we have this we can use them both to create a filter
[Show Top/Bottom N Mfr]
[2016 YoY Sales RANK] <= [SELECT N VALUES]
OR
[2016 YoY Sales RANK] > [2016 YoY Sales SIZE]  [SELECT N VALUES]
And now we can use these on the filter.
I've split these up into seperate fields so you can see how it's all working, but you can nest this all into one calc (if you want)
On your YoY Calculation...we can use a Table Calc to do this, but as you want to use this in the TopN/BottomN sets (and we can't use Table Calcs in Sets) I think this is a good approach. You could make this slight change to make it dynamic (so you won't have to change the calculations each year)
[2016 SALES]
IIF([Invoice Year]= {MAX([Invoice Year])}, [Sales],0)
[2015 SALES]
IIF([Invoice Year]= {MAX([Invoice Year])}1, [Sales],0)
Hope that helps, and makes (some) sense

Top Bottom  SR.twbx 15.5 MB


2. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Oct 4, 2017 2:12 PM (in response to Simon Runc)Thank you so very much Simon  this is fantastic!
This is exactly what I've been looking for. My next issue is that I assumed as I continued to drill down the data would stay sorted by the Mfr Roll up. But when I pulled in Prod Hier Level 3 (on the 2nd tab) it appears it made subcategories for the ranking and the size. Is there something that I can do to just see the Mfr roll up and Product Heir Level 3  sorted by sales amount for Mfr Roll up? Am I now asking too much?
I would like to verify that creating sets to determine my initial top 10 locations is the correct way to go. I ask because I'm going to have to do this exercise in 5 other views. I assume I'll have to make the same top/bottom sets for the other dimensions then.
Also, I would love to be able to nest the entire formula. I, of course, wasn't able to make that work. Can you help me with that?
Once again thank you so much. This has been a great deal of help and I'm now able to begin my first report.
Thanks!
Bekki

Top Bottom  SR THANK YOU.twbx 15.7 MB


3. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Oct 5, 2017 1:02 AM (in response to Bekki McCormick)hi Bekki,
So glad that helped...
With the Rank Function (and Table Calculations in general) it is not actually ranking MFR Roll Up (per se), but is Ranking whatever is in the VizLoD (Viz Level of Detail), and then runs that Rank depending on how you set up the Compute Using (and Partitioning), which in human language is RANK by what (Addressing), and restart Rank every X (Partitioning).
So if you drag a new dimension into the canvas, then you'll need to set up the Rank to take that into account.
Just to check...in your "YOY DRILL DOWN " tab you want it to show the Top/Bottom N MFR Roll Up, and then the [Prd Hierarchy Level 3] for each of these top/bottom N, ranked? i.e. you don't want a further rank to bring back the top/bottom N of [Prod Hierarchy 3], within the Top/Bottom N MRF Roll Up.

4. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Oct 5, 2017 6:38 AM (in response to Simon Runc)Thank you for you quick reply.
You're correct, I do not need to further rank down to the Prod Hierarchy. I only want to see the products involved.

5. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Oct 5, 2017 7:27 AM (in response to Bekki McCormick)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!

Top Bottom  SR THANK YOU.twbx 15.7 MB


6. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Oct 10, 2017 6:45 AM (in response to Simon Runc)YES!!!!!!!!! This is exactly what I needed and it's amazing! Thank you so much. You have helped me out so much!
I tweaked the "Show Top/Bottom N MFRPH3" formula. It now identifies top, bottom or hide.
I've cleaned up the workbook with new data. I wanted to make sure I could recreate everything that you just taught me. I've attached a new copy.
Is it possible to put combine some or all of these calculations?
Again, thank you so much...

TABLEAU Simon testing.twbx 1.6 MB


7. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Nov 14, 2017 3:55 PM (in response to Bekki McCormick)Hi Simon! I'm back with an additional question.
Your solution to my problems was great and what I needed! I was even able to add another layer in the first tab titled member... Thank you so much...
This method currently works for entire year data. My new issue is that I need to be able to do this for specific date ranges.
On the second tab I've played around and added data range parameters and calculations. I was hoping that I could just update the sets to use the "date range sales diff" calculated field I created. However this does not show as a field in the parameter drop down. Is it because if's aggregated? I've also tried the parameter "by formula" and now I'm getting an error message in the new sets that I created (New  Member TOP).
Is there a way to update my sets so that the top/bottom 10 in now calculated on the date range sales difference? Also, above you mentioned something about be able to put all the nested formulas together. Is that an option too or I'm I doing this the best possible way already.
Thank you so much for previous help!
Bekki

8. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Nov 15, 2017 2:05 AM (in response to Bekki McCormick)1 of 1 people found this helpfulhi Bekki,
Glad to hear it did the job, and you've managed to extend it!
So I think we can make this simpler than creating new Sales and QTY calculations for the parameter filtered dates.
I've add the following filter
[Posting Date: Parameter Filter]
[Posting Date] >= [Date Range 1a] and [Posting Date] <= [Date Range 1b]
OR
[Posting Date] >= [Date Range 2a] and [Posting Date] <= [Date Range 2b]
I can bring this onto the filter shelf and set to true. We also need to make this filter a "context" one; this is because sets are computed before any regular filters, so in order to get the filter to affect the set results we need to bump it up the calculation pipeline (making a context filter does this)
As there seems to be a lot going on here...
You can use the fields you've created on the parameter dates and sets, but creating your calculations as a measure and then using this in the set.
eg.
I created this
[Date 2 Sales Diff  FOR SET]
SUM([Date 2 Sales])  SUM([Date 1 Sales])
and then can use this in the set like this
Hope that helps

9. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Nov 15, 2017 8:21 AM (in response to Simon Runc)Hi Simon  This is almost it! The issue is Tableau isn't calculating the Top/bottom correctly because Tableau doesn't recognize blanks as zero. The new formula for the set is not counting the blank fields as 0. I have negative balances that should be included in the top/bottom.
Is there a way to fix this?
Thanks!
Bekki

PIVOT TABLE REVAMP TEMPLATE.twbx 17.9 MB

top 3.PNG 42.3 KB


10. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Nov 15, 2017 8:35 AM (in response to Bekki McCormick)OMG I think I fixed it on my own..... I changed the new set formula to ZN(SUM([Date 2 Sales]))  zn(SUM([Date 1 Sales])).....
Prior to messaging you I was trying it with the lookup function. It wouldn't/t accept that. Does this solution sound right?

11. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Nov 16, 2017 1:24 AM (in response to Bekki McCormick)2 of 2 people found this helpfulNot at my laptop, but that sounds right...good work. Yes the ZN function equates NULLs to Zeros. In Tableau, without the ZN, when you add/subtract...etc 2 fields and one of them is NULL the result will be NULL.
So 10+NULL=NULL, not 10...
but ZN(10)+NZ(NULL)=10

12. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Nov 15, 2017 10:45 AM (in response to Simon Runc)Thank you for all your help! My book is about 20 calculations less...... This is great!
Now these nested calculations are going to drive me nuts! I switch out the pills a lot, therefore I have to update all the nested calcs. I vaguely remember reading online that You can replace a pill with another pill and all the calcs and so on should update. Does this sound true, or was this in one of my Tableau dreams ?
If there's not a different way  I'm assuming It will become second nature as time goes by.

PIVOT TABLE REVAMP TEMPLATE.twbx 17.7 MB


13. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Simon Runc Nov 16, 2017 3:48 AM (in response to Bekki McCormick)1 of 1 people found this helpfulSo part dream!!
If you set up Table Calculations without specifying the dimensions (so using Table Across, Table Down, Pane Down...etc.) then you can switch out pills, as the Table Calc isn't linked to actual fields (of course this also comes with the downside that if you move pills about then the Table Calcs change!). In your case we need to specify the dimensions, due to the complexity of things we are computing.
One way I get round this is to use parameters to switch the dimensions...I've worked up a quick example where I've got % of total set up to use a specific dimension
So if I try and switch Country with State then...it breaks
but if I set them up on a parameter....nothing breaks as the Table Calc is set against the "Selected Level" dim (it doesn't care that I switch what's in here!)
I did try (a few weeks back) to attempt to nest some of your calcs, but due to the fact that we need different table calculations operating of different addressing/partitioning I couldn't get it to work.
Hope that helps.

14. Re: Newbie needs help  Is it Top N  Is it a filter  Is is another Calc ?
Bekki McCormick Nov 16, 2017 11:25 AM (in response to Simon Runc)This is cool! I'm going to find a way to incorporate this into a report.
Thank you for all of your help!