Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Did you mean:
Contributor III

## Need help to prepare Rank list from expression

I need to create rank selection  it make from expression as the table.

1 Solution

Accepted Solutions
MVP

Try with this expression to be exact which is inline with @chrismarlow mentioned

``````=Aggr(
if(((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100)>=120, '>120%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100>=100 and (sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<120,'100-120%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<0 ,'<0%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100>=0 and (sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<100,'<100%')))),
Member_ID)``````

3 Replies
Specialist II

Hi,

Check this post that I think answers your question;

https://community.qlik.com/t5/QlikView-Creating-Analytics/Selection-based-on-calculated-field/td-p/3...

So you should be able to pick up your formulae up from your Rank column & paste into aggr(YOUR FUNCTION, Member_ID), but you would need to unpick any column references you might have used to do the formula (I can't open your QVW as I only have personal edition, so don't know what your formula looks like, but I would have referenced the columns).

Cheers,

Chris.

MVP

Try with this expression to be exact which is inline with @chrismarlow mentioned

``````=Aggr(
if(((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100)>=120, '>120%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100>=100 and (sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<120,'100-120%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<0 ,'<0%',
if((sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100>=0 and (sum({<Member_ID>}Sale)-sum({<Member_ID>}Target))/sum({<Member_ID>}Target)*100<100,'<100%')))),
Member_ID)``````

Contributor III
Author

Thank you Thank you Thank you  for Sunny & Chris.

Community Browser