Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ben_pugh
Creator
Creator

Calculate Nth distinct value of dimension in straight table

Hi everyone.

It's a bit of a strange request, but I'm hoping someone can help.

I have a table as below left, with a dimension limit showing the largest 3 Sum(Sales) values on the Sub-Area field. This gives me 3 entries per Area.

What I would like to do is to split this into separate tables for each value of Area (the three tables on the right). The values in the Area field may change, so I don't want to hard-code them. Essentially what I want is to be able to replicate the table on the left, but only where e.g. the value of the Area field is the second distinct value (in this case 'B') of Area. For simplicity we can assume that there will always be 3 distinct values of Area.

I'm assuming I can do something with aggr() and rank(, but I've had no success so far. Any assistance would be much appreciated!

Capture.PNG

1 Solution

Accepted Solutions
ben_pugh
Creator
Creator
Author

It doesn't matter, I've solved it myself!

If anyone is interested, instead of Area as a dimension, I used:

=if(Aggr(RowNo(TOTAL),Area)=n,Area)

where 'n' is an integer representing the nth instance of the dimension.

View solution in original post

1 Reply
ben_pugh
Creator
Creator
Author

It doesn't matter, I've solved it myself!

If anyone is interested, instead of Area as a dimension, I used:

=if(Aggr(RowNo(TOTAL),Area)=n,Area)

where 'n' is an integer representing the nth instance of the dimension.