Showing top N items horizontally

[jstensig]

Hi
Currently I have a straight table showing Top 5 most sold items for each Customer 

However I´ve been asked to show it a bit different as shown below

Still I think it should be a straight table with dimensions CustomerNo and CustomerName, and then 5 expressions, one for each of the 5 rankings

My "not working" try for Rank 1:
If(Rank(Aggr(Sum(Sales), ItemNo, CustomerNo )) =1 , only(ItemNo))

[Andrei_Cusnir]

I believe that your approach might not give you the desired outcome. In this use case scenario you will need to use a Pivot Table in combination with set analysis. I don't have the same dataset as you do, however I managed to transfer the Straight Table as you have, into a Pivot Table and the outcome is the one that you are looking for. Here are the steps that I have followed, after that you can follow the same approach on your side:

I have loaded simply the data that you showed in the screenshot with "LOAD * INLINE" statement and this is the resulted table:

Then I have created a new Pivot Table with the following dimensions:

After that I have created the following expressions:

1. Label: Rank 1, Definition: Only({<Rank={1}>} ItemNo)
2. Label: Rank 2, Definition: Only({<Rank={2}>} ItemNo)
3. Label: Rank 3, Definition: Only({<Rank={3}>} ItemNo)
4. Label: Rank 4, Definition: Only({<Rank={4}>} ItemNo)
5. Label: Rank 5, Definition: Only({<Rank={5}>} ItemNo)

The resulted Pivot Table looks like this:

I hope that this information is helpful

[stevejoyce · Accepted solution]

Try adding an outside aggregation with total to get your ranks

=concat(distinct total <CustomerNo, CustomerName> If(aggr(nodistinct rank(Sum(Sales)), CustomerNo, CustomerName, ItemNo)=1, ItemNo))

=concat(distinct total <CustomerNo, CustomerName> If(aggr(nodistinct rank(Sum(Sales)), CustomerNo, CustomerName, ItemNo)=2, ItemNo))

...

[MayilVahanan]

Hi @jstensig 

Try like below

Use Pivot table,

Dim1: CustomerNo

Dim2: CustomerName

Dim3: =aggr(if(rank(Sum(Sales))<=6,rank(Sum(Sales))),CustomerNo,ItemNo)

Exp: Only(ItemNo)

Then Drag the Dim3 to top . 

Hope it helps. 

[jstensig]

Hi folks
Thank you for the different suggestions. For me the right solution was provided by Steve, as it gives the best performance in my rather large data model and good flexibility.  I still use a straight table, so I have to make an expression for each rank but I can live with that