Calculate Nth distinct value of dimension in straight table

[ben_pugh]

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!

[ben_pugh · Accepted solution]

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.