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: 
panwipa2528
Contributor III
Contributor III

Need help to prepare Rank list from expression

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

bb.jpg

1 Solution

Accepted Solutions
sunny_talwar

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)

 

View solution in original post

3 Replies
chrismarlow
Specialist II
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.

sunny_talwar

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)

 

panwipa2528
Contributor III
Contributor III
Author

Thank you Thank you Thank you  for Sunny & Chris.