Top 5 Sales in measure level

[24_02_SRR]

Hi All,

We have to show the top 5 Sales value with the ID and Name value in the measure column.  Sale Value is the addition of Sales1 and Sales 2 column in data.

Sample output:

Level	Company	Measure
L1	A	0027-XXX- 88000 Sales value 0065-YY-75000 Sales Value 0012-AAA-60000 Sales Value 0054-FFF-45000 Sales Value 0065-KKK-23000 Sales Value
L1	B	0087-XXX- 188000 Sales value 0095-YY-175000 Sales Value 0052-AAA-160000 Sales Value 0034-FFF-145000 Sales Value 0095-KKK-123000 Sales Value

Measure Column: ID-Name-(Sales1+Sales2)

How do we achieve this logic in Qlik Sense Pivot table? 

[BrunPierre]

Add these lines to the load script,

ID & '-' & Name as IdName

(Sales1+Sales2) as TotalSales

ad try with this.

MaxString({<IdName= {"=Rank(Sum(TotalSales)) <= 5"}>} IdName) & '-' &Sum({<IdName= {"=Rank(Sum(TotalSales)) <=5"}>} TotalSales) &' Sales Value'

[24_02_SRR]

Hi BrunPierre,

Thanks for your help.

We don't have ID and Name dimensions in Pivot, So the above measure won't work for us.

It gives the sum of top 5 values but we need top 5 ID and Name with respective Sales Value not total of 5. 

Any other Solution?

[MayilVahanan]

Hi

Try like below

Concat(Aggr(ID&'-'&Name&'-'&Sum(Sales1+Sales2), ID, Name, Level, Company), chr(10))

If it display the output as you expected, include the Rank() function to filter only top 5 information.

Hope it gives some idea to you

[24_02_SRR]

Hi MayiVahanan,

Concat(Aggr(ID&'-'&Name&'-'&Sum(Sales1+Sales2), ID, Name, Level, Company), chr(10))

This work for me 90%. I'm getting all the ID's here.

I couldn't restrict top 5 here.

can you help me on this 

[BrunPierre · Accepted solution]

Concat(Aggr(ID&'-'&Name&'-'&Sum(Sales+Sales2) <= 5, ID, Name, Level, Company), Chr(10))

[24_02_SRR]

BrunPierre & MayilVahanan

Thank you for your help.

[Kushal_Chawda]

@24_02_SRR  Is this solution really working for you? I just run on TEST data it's not giving result as expected.. I am not sure how it is correct because it is nowhere using rank function which is required

[Kushal_Chawda]

@24_02_SRR  this should be the required expression

=Concat(aggr(if(aggr(rank( (sum(Sale1)+sum(Sale2)),4), Level, Company,ID)<=5,ID&'-'& Name &'-'& (sum(Sale1)+sum(Sale2))),Level, Company,ID),chr(10))