Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Binay_Sharma
Contributor III
Contributor III

Sorting and finding out top 10 customers in Pivot Table based on the Total of YTD value

Hi,

I spent enough time on this, but couldn't come up with exact solution. 

I have created a pivot table as follow:

Binay_Sharma_0-1619654223709.png

Above, Gross fee which is the measures across the YTD months is calculated as below:

sum({<[Cycle End Date]={'>=$(vCurrentYearStart)'},[_Acct_Appl_Code_Flag] ={1} >} [#uvGross Fee])

I am strugging to sort the data descending based on the Total. The Total field is something which is calculated automatically from Qliksense feature. 

can someone please help me , how to pick the top 10 customers here? I looked as some articles, and talks about Rank, but wasn't clear for me.

I would really appreicate if someone can provide some insights. 

Or, I have to thik outside the box and is it something easily achieved by plain table? Please advise. 

Thanks,

Binay 

 

 

2 Replies
MayilVahanan

HI @Binay_Sharma 

Try like below

In Dim:

=Aggr(If(Rank(sum({<[Cycle End Date]={'>=$(vCurrentYearStart)'},[_Acct_Appl_Code_Flag] ={1} >} [#uvGross Fee]))<=10, CustomerName), CustomerName)

Sorting:

For Customer Dim:

MayilVahanan_0-1619663653522.png

Exp: sum({<[Cycle End Date]={'>=$(vCurrentYearStart)'},[_Acct_Appl_Code_Flag] ={1} >} [#uvGross Fee])

Hope it helps

 

Thanks & Regards,
Mayil Vahanan R
Binay_Sharma
Contributor III
Contributor III
Author

Thank you Mayil. I will apply these and see if it works. Will let you know. 

 

Thanks again.

Binay