topic Find minimum &max ranked to use in text box in QlikView

Find minimum &max ranked to use in text box

sstenglerbcm — Mon, 05 Aug 2013 19:46:12 GMT

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

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!

Re: Find minimum &max ranked to use in text box

jjordaan — Mon, 05 Aug 2013 20:04:29 GMT

Hi Sam,

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

Re: Find minimum &max ranked to use in text box

sstenglerbcm — Mon, 05 Aug 2013 20:44:05 GMT

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

Re: Find minimum &max ranked to use in text box

sstenglerbcm — Mon, 05 Aug 2013 20:46:51 GMT

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

Re: Find minimum &max ranked to use in text box

sstenglerbcm — Mon, 05 Aug 2013 20:55:01 GMT

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...

Re: Find minimum &max ranked to use in text box

swuehl — Mon, 05 Aug 2013 20:58:25 GMT

Try negating the sum.

And for your other question:

Maybe like this:

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

Re: Find minimum &max ranked to use in text box

jjordaan — Mon, 05 Aug 2013 21:02:58 GMT

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