12 Replies Latest reply on Sep 3, 2018 8:14 AM by Agustin Leira

    Use of TOP N with filters applied

    Agustin Leira

      Hi everybody, how are you?

       

      I don't know how set the Top N filters for each site filtered. For example, when I put 32 in TOP N, I want to see the 32 rows of MLB.

       

      I just see 14 rows of them because the others rows shows  to the other sites: MLA, MCO, MLC, etc.

       

      Screen Shot 2018-08-31 at 14.23.37.png

       

      How can achieve this?

       

      Thank you so much!

        • 1. Re: Use of TOP N with filters applied
          meenu choudhary

          HI Agustin,

           

          I tried to replicate the above scenario using superstore. Below i want to see "Top n" products in select "sub-category".

           

          1. Create parameter.

          2. rank sale = rank(SUM([Sales]))<= [Select top]

          3. Drag rank sale to filter and select "True".

           

           

           

          • 2. Re: Use of TOP N with filters applied
            Simon Runc

            hi Agustin,

             

            If you change your filters to be "context" filters (click on the dimension in the filter and you'll see an option "add to context"...it will also go gray/brown to indicate the filter is now "in context"). Top N filters are applied before any regular filters are applied, but adding a filter to context bumps it up the calculation pipeline, so it gets applied before the Top N is computed...if you wondered why this happens)

             

            hope that helps

            • 3. Re: Use of TOP N with filters applied
              Ritesh Bisht

              Hi Augustin,

               

              I have my old blog related to TOP N Vs Context filter

               

               

              Screen Shot 2018-08-31 at 11.20.50 PM.png

               

              Order of Operations

               

              Hope it helps !

               

              Thanks,

              Ritesh

              • 4. Re: Use of TOP N with filters applied
                Agustin Leira

                Thanks for answer me! Sadly I can't achieve this.

                 

                I attach the file to get your help!

                 

                Could you help me?

                • 5. Re: Use of TOP N with filters applied
                  Simon Runc

                  hi Augustin,

                   

                  Here you go. I've added all the filters (except the one with the Top N) to context. Now one thing I noticed is that your %age of Total calculation has changed. This is because it's a FIXED LoD, which like Top N are also computed before the regular filtering, so the effect we get is that the FIXED LoD that makes up the "Total" (denominator) is now no longer the global total. Either use the solution suggested by Meenu (where by using a table calculation filter you can still access "all" the data), or possibly hard code that "total" value in the data source (I noticed a bit of custom SQL when I opened the file, so it might be an option...it's all the FIXED LoD is doing anyway!!)

                   

                  Hope that helps

                  • 6. Re: Use of TOP N with filters applied
                    Agustin Leira

                    Thanks Simon! My mistake were that I have added all the filters to context, not excluded one with the Top N.

                     

                    The problem right now is that I lost the 'Distribution of status' by LoD.

                     

                    What do you suggest me to fix them?

                    • 7. Re: Use of TOP N with filters applied
                      Simon Runc

                      So to do this we'll have to use a different technique. In the same way the LoD "Exploited" the way that FIXED LoDs are computed before any (regular) filter is applied, we can exploit the way that Table Calculation Filters are applied after filtering (this has the affect of "hiding" the unwanted rows, but still gives us access to the underlying data.

                       

                      So first we re-create the %age Calculation using a table calculation

                      [% shipment por status_TC]

                      SUM([qty_shipment])

                      /

                      TOTAL(SUM([qty_shipment]))

                       

                      Then we can use the RANK function to RANK the entries (I've used RANK_UNIQUE so you always get back 32 rows, even if 2, or more, are tied)

                      [Qty_Shipment_RANKUNIQUE]

                      RANK_UNIQUE(SUM([qty_shipment]))

                       

                      and then we can use this as a Table Calculation Filter

                      [Qty_Shipment_RANKUNIQUE_Filter]

                      [Qty_Shipment_RANKUNIQUE]<=[Top N]

                       

                      We could have done the last 2 in a single calculation, but I've split it out to help explain what's going on.

                       

                      As the Table Calc filter is applied last, the %age Calculation is still over all the data (that hasn't been filtered out by the other filters).

                       

                      Hope that does the trick and makes sense

                      • 8. Re: Use of TOP N with filters applied
                        Agustin Leira

                        Thank you so so much for your help Simon!!

                         

                        Realy, I am learning about this issues/topics.

                         

                        The problem right now with the file that you sent me it's that I lost the dimensionality for substatus:

                        Screen Shot 2018-09-01 at 12.16.59.png

                        And when I added, I unset the rank_unique.

                         

                        How can I set this too see well?

                        • 9. Re: Use of TOP N with filters applied
                          Simon Runc

                          OK so this gets a little more complicated! The reason this is happening is that [SHP_SUBSTATUS_ID] is now in the VizLoD (Viz Level of Detail) and so the RANK_UNIQUE is run over both [SHP_SUBSTATUS_ID] and [agencia_origen]

                           

                          So we're going to have to use a slightly different trick. Tableau sorts "headers" in ascending order (just naturally) so the negative of the SUM of [agencia_origen] will order things in descending order. Now we need to use a WINDOW_SUM so we can SUM over the [SHP_SUBSTATUS_ID] and return a single value for each [agencia_origen], so we create

                          [Qty_Shipment_WINDOW_SUM Sorter]

                          ZN(WINDOW_SUM(SUM([qty_shipment])))*-1

                          Set up with the following set up

                           

                          and this creates this

                           

                           

                          Now we have them in the right order, we can use index() to act as our rank

                          [Qty_Shipment_Index (AS RANK)]

                          index()

                           

                          we set this up like this

                           

                           

                          Then we can create the < Filter and set to true (on the filter)

                          [Qty_Shipment_Index (AS RANK)]<=[Top N]

                           

                          Hope that makes (some) sense!

                          • 10. Re: Use of TOP N with filters applied
                            Agustin Leira

                            Thank you Simon! Now it's okey. I achieve that I wanted!!

                             

                            Are there any way to hide the values of first and second column in the table?

                             

                            Screen Shot 2018-09-03 at 09.56.30.png

                            • 11. Re: Use of TOP N with filters applied
                              Simon Runc

                              Cool...glad it worked.

                               

                              Yes just untick the "show header" option in the pill (just click on the pill and you'll see the "show header" ticked, just untick it). I really just left them visible so you could see what it was all doing.

                              • 12. Re: Use of TOP N with filters applied
                                Agustin Leira

                                Thank you so so much for your help!!