Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Straight table top 10 with multiple dimensions

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!

4 Replies
sonysree88
Creator II
Creator II

Hi ,

Just right click on the straight table and go to the dimension limits tab here you can able to see the below window then you should select the greatest 10 here to get the top 10 values

dimension limits.PNG

Regards,

Sony

Anonymous
Not applicable
Author

Qlik Sense not Qlik View

Not applicable
Author

Hi David,

I'd love to know how you implemented this... I'm having a very similar problem.

Most pressingly, I'd like to know how you managed to get the 'Others' to show at the bottom. I'm doing something similar and ranking by a specific measure (e.g. Sum(Sales)), but the 'Others' will appear according to the sort as opposed to the bottom of the table.

Is it possible to send an example .qvf? I'd be extremely grateful!

Many Thanks,

Qlik User

Anonymous
Not applicable
Author

Please find attached - be aware at as per the linked thread that the sorting does not work as you would expect, the Top N overrides whatever sort order the user would like to apply sadly.