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: 
sstenglerbcm
Contributor
Contributor

Find minimum &max ranked to use in text box

Below is a table of data that I've loade in to a QVW:

RankExample.png

I need to have a couple of text boxes showing various peices of info - namely, the top ranked 'Branch', with it's 'GP', and the other two fields - each in it's own text box.

In addition, I'll need the lowest ranked 'Branch', and it's associated fields.

I've got an expression using Rank() to pick the highest ranked Branch, but I'm not sure how to pick the lowest (based on GP or $ Increase):

=aggr(if(rank(sum({1<PeriodYear = {2013}>} GP))<=1,Branch),Branch)

Thanks for your help!

S.

6 Replies
jjordaan
Partner - Specialist
Partner - Specialist

Hi Sam,

Try this =aggr(if(-rank(sum({1<PeriodYear = {2013}>} GP))<=1,Branch),Branch)

sstenglerbcm
Contributor
Contributor
Author

Jeroen,

thanks, I'll try it - one other question - can this expression be used as part of a set analysis expression?

Such as:

=sum({<Branch = aggr(if(-rank(sum({1<PeriodYear = {2013}>} GP))<=1,Branch),Branch)>} $ Increase)

Sam

sstenglerbcm
Contributor
Contributor
Author

Sorry, putting a minus sign in front of Rank()  didn't work -

S.

sstenglerbcm
Contributor
Contributor
Author

However, putting a minus in from of sum() did, somewhat. But because I have three Branches that have $0.00 GP, I don't get anything displayed in the text field unless I do a CONCAT().

I'll have to see if I can use the expression for $ Increase and% Increase...

S.

swuehl
MVP
MVP

Try negating the sum.

And for your other question:

Maybe like this:

=sum({<Branch = {"=rank(sum({1<PeriodYear = {2013} >} GP)=1"} >} [$ Increase])

jjordaan
Partner - Specialist
Partner - Specialist

Sorry Swuehl was wright. You should negative the Sum and not the Rank.