4 Replies Latest reply: Jun 12, 2017 7:37 PM by David Maurice RSS

    Straight table top 10 with multiple dimensions

    David Maurice

      Just a quick walkthrough of a problem we had with a straight table where the solution wasn't immediately obvious.

       

      We've got a table where we want to show users the top N customers by whatever sort they put on the table. With one dimension in the data, this works as expected and easily, with the "Show Others" option ticked holding the remaining balances.

       

      However, each entry (let's say a sale record) has dimensions like Country, Customer, which would be good for filtering but in this context from a individual sale standpoint, there is a 1:1 mapping between the two fields. Doing a top 10 on sales however treats each of these related dimensions as a grouping field, which in this context is not required (and confusing).

       

      For example:

      Sales:
      load * inline
      [ SaleID, CustomerID, SalesAmount
      1,1,100
      2,1,500
      3,1,1000
      4,2,50
      5,2,700
      6,2,20
      7,2,40
      8,3,50
      9,4,40
      ];

      SalesCustomers:
      load * Inline
      [ CustomerID, CountryID
      1, 1
      2, 1
      3, 2
      4, 3
      ];

       

      Using this data, we can create a table with SalesID as dimension, and sum(SalesAmount), showing the top 5 by sum(SalesAmount) descending to get the first picture, great!

       

      So let's add the other two fields - users would want to filter on them, and they provide more information. Adding CustomerID and CountryID as a dimension, we get the second picture. Not so great now, as there are subtotals for each customer. We just want to show the remaining sales total, without the split by CustomerID.

       

      Now, what if we add a limit on CustomerID? With the same logic we are using for SalesID? We will do that, except given our number of customers, let's pick top 2. This gives the third picture, which again does grouping.

       

       

      So, this was originally intended to be a question, but I think I've worked it out. Given that there is a 1:1 mapping between these, what if we went through on all the child dimensions, and limited them to one value? So by going into each of the other dimensions, limiting them to the top 1 record with others enabled forces Qlik to only show others, which gives us the solution we want in the fourth picture. This I imagine could be extended to 1:N relationships, but YMMV. There are also other solutions - concatenating the dimensions as text in a single dimension would also work, but you lose the filtering and clarity.

       

      Hope this helps, because it's baffled me for the past year! My boss got it working using the dimensions as concatenated measures and converting from a pivot table somehow, so this at least works rather than some dark art he did!