14 Replies Latest reply on Nov 16, 2017 11:25 AM by Bekki McCormick

    Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?

    Bekki McCormick

      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!


        • 1. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
          Simon Runc

          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])



          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]


          [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

          • 2. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
            Bekki McCormick

            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.




            • 3. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
              Simon Runc

              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

                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

                  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]


                  [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!

                  1 of 1 people found this helpful
                  • 6. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                    Bekki McCormick

                    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 MFR-PH3" 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...

                    • 7. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                      Bekki McCormick

                      Simon Runc  


                      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!



                      • 8. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                        Simon Runc

                        hi 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]


                        [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.



                        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

                        1 of 1 people found this helpful
                        • 9. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                          Bekki McCormick

                          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?




                          • 10. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                            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

                              Not 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

                              2 of 2 people found this helpful
                              • 12. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                                Bekki McCormick

                                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.

                                • 13. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                                  Simon Runc

                                  So 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


                                  TC Breaks on Switch.gif


                                  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!)


                                  TC Switch.gif


                                  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.

                                  1 of 1 people found this helpful
                                  • 14. Re: Newbie needs help - Is it Top N - Is it a filter - Is is another Calc ?
                                    Bekki McCormick

                                    This is cool! I'm going to find a way to incorporate this into a report.


                                    Thank you for all of your help!