Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
Partner - Creator III

How to find sum Top n value of each dimension?

Hello Qlikers,

Greetings..!!

I wanted to find spend of top 2 customers for each category. I tried using the expression,


Sum({<CUSTOMER = {"=rank(Aggr(sum(Value),CUSTOMER,CATEGORY))<=2"}>} Value)

this expression is working fine when I select any category but when I remove selection expected value isn't correct. I have attached sample .qvf with this discussion for your convenience.

Looking for help..!!

Thanks and Regards,

Mohan

8 Replies
sunny_talwar

Try this

Sum(Aggr(If(Rank(Sum(Value)) <= 2, Value), CATEGORY, CUSTOMER))

mohan_1105
Partner - Creator III
Partner - Creator III
Author

Hi Sunny,

Thanks for your response. Your expression works good in the attached sample data, but it doesn't work in my actual application. Do you have any idea why?

Thanks,

Mohan

sunny_talwar

It is giving you an error or what exactly is the issue?

mohan_1105
Partner - Creator III
Partner - Creator III
Author

Sunny, It is not giving any error. The issue is the expression isn't giving expected output in my application but it seems to be good in the sample application you have attached.

sunny_talwar

I guess tell us what you are getting and what you expect to see. Unless you give those details, I will have no idea what might be going on.

mohan_1105
Partner - Creator III
Partner - Creator III
Author

Hi sunny,

Please let me know if the snippets aren't explaining better.pture.JPG

If you see this sheet, I used the expression you suggested. Expression output isn't giving the correct value for the category like furniture, paper, phones, etc.

I have crosschecked the same by filter the category,

Capte.JPG

I hope you're clear on my issue.

I checked the format of the Sales, everything is fine.

Regards,

Mohan

sunny_talwar

Try this may be

Sum(Aggr(If(Rank(Sum(Value)) <= 2, Sum(Value)), CATEGORY, CUSTOMER))

mohan_1105
Partner - Creator III
Partner - Creator III
Author

Sunny,

Thank you very much. Now, I'm getting the expected output.

Regards,

Mohan