topic Re: Sorting and finding out top 10 customers in Pivot Table based on the Total of YTD value in App Development

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

Binay_Sharma — Thu, 29 Apr 2021 00:01:02 GMT

Hi,

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

I have created a pivot table as follow:

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

MayilVahanan — Thu, 29 Apr 2021 02:34:56 GMT

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:

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

Hope it helps

Binay_Sharma — Thu, 29 Apr 2021 14:59:51 GMT

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

Thanks again.

Binay