1 Reply Latest reply on Dec 13, 2016 4:31 AM by Simon Runc

    Top N when looking by multiple dimensions including Customer

    Nicola Prime

      Hi All,

       

      So I have some further queries on Top 'N'...

       

      I created by Top N successfully but when I start to add additional filters, this caused some issues. I managed to correct this by going into the 'Advanced' portion of the Index formula I created and adding these dimensions into the sorting.

      I used the "Use Advanced Options in the Calculation (Alternative Example)" http://kb.tableau.com/articles/knowledgebase/finding-top-n-within-category

       

      However, I now have an issue when I want to split out by customer. My aim is identify the top 5 SKUs by volume as a whole, but to also show the split of that total volume between customers.

       

      Below is an example of what happened when I added "customer" to the view. You can see that Ranking 6 total adds up to more than ranking 5. Tableau is clever enough to identify that this should be one of the top skus but somehow failed to rank it before the SKU identified as Rank 5. Any ideas on how I can correct this?

       

      Also in relation to Top N issues is the following post: Top 'N' based on a specific month with comparisons in previous/next month for same SKUs - if anyone has any ideas on this, I would be so grateful!

       

      Thanks!

       

      Nicola

        • 1. Re: Top N when looking by multiple dimensions including Customer
          Simon Runc

          hi Nicola,

           

          So yes this is a bit of a tricky one...where you want to RANK at a certain level (Product) but the VizLoD is at another (Product/Customer).

           

          One way to do this is as follows...

           

          First I set up a Window_Sum on Quantity so I can return a single quantity value for each product (and not a value per customer/product)

          [Quantity WS]

          WINDOW_SUM(SUM([Quantity]))

           

          I then set up a RANK, using this field (and I use RANK_DENSE here so I get a single RANK per product)

          [Quantity RANK]

          RANK_DENSE([Quantity WS])

           

          Now as I've nested the [Quantity WS] in the [Quantity RANK], I get the option to set up the compute using differently (the redbox shows where you can access each calculation)...

          So we set up the [Quantity WS] like

          and the [Quantity RANK] as Table Down (or include all dimensions if your pill arrangement isn't the same as my example)

           

          Then we can just filter on the [Quantity RANK] and show just 1-5...the 'Product Rank' tabs shows just the Product Ranks, so you can verify its picked up the correct 5 products.

           

          As a simple alternative...I've also included a version just using the Top N filter built into Tableau (and also used the negative of the Window_Sum to sort them)...

           

          This tends to not give the fine-grained control of using index/rank, but might work and is much much easier!!!

           

          Hope that helps and makes sense, but let me know if not