topic Showing top N items horizontally in QlikView

Showing top N items horizontally

jstensig — Tue, 07 Sep 2021 13:28:27 GMT

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))

Re: Showing top N items horizontally

Andrei_Cusnir — Wed, 08 Sep 2021 14:54:06 GMT

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:

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

The resulted Pivot Table looks like this:

I hope that this information is helpful

Re: Showing top N items horizontally

stevejoyce — Wed, 08 Sep 2021 15:55:25 GMT

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))

...

Re: Showing top N items horizontally

MayilVahanan — Thu, 09 Sep 2021 03:13:30 GMT

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.

Re: Showing top N items horizontally

jstensig — Thu, 09 Sep 2021 06:48:19 GMT

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