Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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
Please close the thread by marking correct answer & give likes if you like the post.
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