1 2 Previous Next 27 Replies Latest reply on Oct 29, 2018 11:17 AM by Joe Oppelt

    Calculate rank unique at higher level and show city detail of Top N

    raoul.tller

      Hi,

       

      Currently I have this dashboard with Top N countries, then a sheet that shows the state details.

      filter1.png

       

      I created a dashboard filter to filter on hover over the countries which works as I want.

      filter2hover.png

       

      But when I hover away (no filter) it always shows ALL countries and states but instead I would only want to show the Top N Country and the states that belong to this. I have tried working with this:

      https://kb.tableau.com/articles/issue/calculating-rank-at-a-level-other-than-the-highest-level-of-granularity

       

      But cannot get the expected result, any ideas?

       

      Top N Countries (Sales) - show city (Sales) of the Top N

        • 1. Re: Top N dashboard action filtering
          Ben Bird

          There is a quick and easy fix for this

           

          click Dashboard > Actions

           

           

          Tableau will have generated for you an action for your filter, select this action for editing

           

          once in the editing window simply select either 'leave filter' or 'exclude all values' from the 'Clearing the selection will:' menu.

           

           

          The default of this menu is 'show all values' which is giving you your undesired result

           

          Hope this helps, best of luck.

          • 2. Re: Top N dashboard action filtering
            Joe Oppelt

            Edit your action to exclude all values when you leave:

             

            • 3. Re: Top N dashboard action filtering
              raoul.tller

              I know about Exclude values but this is not what I am trying to achieve. I want to see the selected Top N and sales split per City on the details when the end-user is not hovering over, currently it shows ALL data country - city, it should just show the selected Top N based on Country with city split out.

               

              So for example with Top 3 France, Netherlands, Spain, it should show France + all france cities, NL + all NL cities, Spain + Spain Cities, nothing more.

              • 4. Re: Top N dashboard action filtering
                Joe Oppelt

                You need to filter the detail sheet so that only those 3 countries remain as the default.


                See attached.

                1 of 1 people found this helpful
                • 5. Re: Top N dashboard action filtering
                  Joe Oppelt

                  What I did in there.

                   

                  First, I applied the ship date filter from topn to the detail sheet.

                   

                  Then, in the detail sheet, I created a sum of country calc.  That's the basis on what you want the three countries to remain in the detail sheet.  I displayed that in TEXT.

                   

                  Notice the table calc settings for that calc.  Cycle through country and state, restarting every Country.

                   

                  Next I created a TOPN calc similar to the one you have on the first sheet.  Only instead of using index, I RANKed the Country Sum calc, and grabbed the top 3.  Notice the table calc settings for this one.  I am cycling through Country and State, but this time I am doing it AT THE LEVEL of Country instead of restarting every country.  (I want all the values within a country to be considered one "item" to be ranked.

                   

                  I just saw that you have a [Sort] calc.  You can incorporate that into this if you want.  However, you can also change my calc from this:

                   

                  RANK([Country sales],'desc') <= [Top N]

                   

                  to this

                   

                  RANK([Country sales],[Parameters].[Sort]) <= [Top N]

                   

                  Also in the table calc setting for topfilter(copy) there is a pulldown for nested calcs.  Select [Country sales] from that pulldown and make sure it has the same table calc setting as the actual pill on the sheet.  So yes, you can have table calcs contained with a table calc, and one would operate one way, and another would operate another.  It's pretty powerful when you grasp this concept!

                   

                  Once I was happy with the generates value of the topfilter(copy) calc, I dragged that to filters and selected for TRUE.

                  2 of 2 people found this helpful
                  • 6. Re: Calculate rank unique at higher level and show city detail of Top N
                    raoul.tller

                    Hi Joe,

                     

                    Thanks for the clear explanation, I have it working on my clients data now as well (wasn't fully behaving like I wanted at first, but now it is).

                     

                    I just noticed two things:

                    1) The grand total does not update to the actual topN, which I have encountered before.

                    2) The ascending/descending is not working properly for me on the details (it shows number 3 first then number 2 then number 5), I will fiddle around with this and see if I can get this to be sorted correctly aswell (descending, based on total).

                     

                    edit:

                    Fixed the sorting.

                    • 7. Re: Calculate rank unique at higher level and show city detail of Top N
                      raoul.tller

                      Is it possible to add the Sales of Day -1 for France, Netherlands, Spain aswell, so not the actual top N sales of day-1 but the Sales Day-1 for the current top N? I tried LOOKUP(ZN(SUM([Sales])), -1) but that does not work.

                      • 8. Re: Calculate rank unique at higher level and show city detail of Top N
                        Joe Oppelt

                         

                        1) The grand total does not update to the actual topN, which I have encountered before.

                         

                        You're right about that.  You're getting the sum of everything, not just the sum of top-N

                         

                        The reason for that is because we're using a table calc to filter.  Messing with INDEX() or RANK() (or a whole bunch of other things) uses table calcs.  And a table calc needs the whole table underlying the sheet so that you can pick out the top-5 of everything that's in there.  A table calc filter's interaction with the table is essential to let other table calcs to operate properly, but it also impacts things like internally-generated grand totals because those are also using a table calc behind the scenes.  And the overall sum of all the countries is getting generated there.

                         

                        Is the total essential for this viz?  If so, the best (and sometimes the only) way to get an accurate grand total is to create a second sheet and display it with the actual data sheet on the dashboard.

                        • 9. Re: Calculate rank unique at higher level and show city detail of Top N
                          Joe Oppelt

                          raoul.tller wrote:

                           

                          Is it possible to add the Sales of Day -1 for France, Netherlands, Spain aswell, so not the actual top N sales of day-1 but the Sales Day-1 for the current top N? I tried LOOKUP(ZN(SUM([Sales])), -1) but that does not work.

                          I'm not understanding the question.

                           

                          The overall sum of these three countries is 7432, correct?  Is that what you are looking to add to the viz?  And if so, where would you want to see it?

                          • 10. Re: Calculate rank unique at higher level and show city detail of Top N
                            raoul.tller

                            No I am looking to add the values for Sales from France, NL and Spain of the day prior to the current ship date?

                            For example Top 3 on 31-12-2018 =

                            France 100

                            NL 75

                            Spain 50

                             

                            These same countries on 30-12-2018 (so its not the Top N of 30-12-2018):

                            France 30

                            NL 80

                            Spain 60

                             

                            So I would like to see what the Sales amount was -1 day. Normally I would just lookup the previous value with LOOKUP.

                            • 11. Re: Calculate rank unique at higher level and show city detail of Top N
                              Joe Oppelt

                              Right now the two sheets have a standard filter on them, limiting the available rows in the underlying table to Dec31 only.

                               

                              Which sheet do you want to see yesterday's sales on?  We can do a   table calc filter that leaves all the underlying rows intact, and just display one day from the table, and then yesterday's data is also available for a lookup -1.

                              • 12. Re: Calculate rank unique at higher level and show city detail of Top N
                                raoul.tller

                                Yes that is what I was thinking, the filters filter out any other dates.

                                 

                                Is it possible to do it for both sheets?

                                • 13. Re: Calculate rank unique at higher level and show city detail of Top N
                                  Joe Oppelt

                                  Looking as the second sheet, the data as we have it now is kind of thin.  A display of 12/31 and 12/30 for the three countries shows this:

                                   

                                   

                                  I'm assuming in your actual data set the data is more robust and would be more meaningful.


                                  But the short answer to your question is yes, you can get yesterday's data.  It will complicate your calcs, but it can be done.

                                   

                                  Right now I'm using LOOKUP as a user filter to select 2 dates, but we can't apply a table calc filter to multiple sheets.  Each table calc is unique to the sheet it is on.  (If a table calc is on separate sheets, each occurrence is evaluated in the context of the sheet it's on.)  But we can drive the date choice with a parameter and have table calcs pick up that value and act accordingly, and one driver would therefore govern both sheets.

                                   

                                  For now the attached is how I generated the screen shot you see.

                                  1 of 1 people found this helpful
                                  • 14. Re: Calculate rank unique at higher level and show city detail of Top N
                                    raoul.tller

                                    Hi Joe,

                                     

                                    Thanks again for the explanation. In my dataset there will always be data available for the date -1.

                                    Also I use a date SET (Last N Amount of business days) in my dataset instead of a simple datafilter, we are doing this so that when the end-user opens the workbook it will always show the most recent data available.

                                    I have mocked up some data and tried to get sheet 1 to only show me the top 5 for 5-10-2018 and its amounts for 4-10-2018 but it shows me this:

                                    top n.png

                                    As you see it shows the actual top 5 of both dates where I would only want to see client 161, 150, 198, 133, 402 for both dates, if that makes sense.

                                    1 2 Previous Next