topic Re: Rank Between Values in QlikView

Rank Between Values

Mon, 30 Jun 2014 17:51:49 GMT

Hi, I'm new to Qlikview and need help with a rank function. I'm trying to rank between values. For example, the store Ranking is from 1 to 391 based on YTD Sales. I would like to see the total YTD Sales for the stores ranked between 50 and 100. I was trying the following but obviously I'm missing something.

=aggr(if(Rank(sum([YTD POS Sales]))<=50and(sum([YTD POS Sales]))>=100,[Store Nbr]),[Store Nbr])

Thank you in advance!

Re: Rank Between Values

maxgro — Mon, 30 Jun 2014 19:03:55 GMT

sum(aggr(

if(aggr(rank(sum([YTD POS Sales])),[Store Nbr])>=50

and aggr(rank(sum([YTD POS Sales])), [Store Nbr])<=100,

sum([YTD POS Sales])), [Store Nbr]))

Re: Rank Between Values

vgutkovsky — Mon, 30 Jun 2014 19:40:36 GMT

Massimo's expression should work, but it's a bit heavy because of the extra aggrs. Here's one that's a little lighter and does the same thing with 1 aggr:

sum(aggr(

if(rank(sum([YTD POS Sales]),4,1)>=50 and rank(sum([YTD POS Sales]),4,1)<=100,sum([YTD POS Sales]))

,[Store Nbr]

))

Regards,

Vlad

Re: Rank Between Values

maxgro — Mon, 30 Jun 2014 19:59:19 GMT

yes, there is an extra aggr in my expr, thanks Vlad

Re: Rank Between Values

Mon, 30 Jun 2014 20:16:55 GMT

Thank Guys. It says the expression is okay but the chart body says "Allocated memory exceeded". Any idea how to fix that?

Thanks again.

Re: Rank Between Values

vgutkovsky — Mon, 30 Jun 2014 21:06:14 GMT

Which version of the expression are you using?

Re: Rank Between Values

Tue, 01 Jul 2014 00:55:51 GMT

The one you provided.

Re: Rank Between Values

vgutkovsky — Tue, 01 Jul 2014 01:33:14 GMT

How many stores are in your data? And are you reducing the data through selections or trying to calculate over all data)?

Re: Rank Between Values

Tue, 01 Jul 2014 01:44:57 GMT

There are 391 stores in the data and calculating over all ytd sales.

Re: Rank Between Values

vgutkovsky — Tue, 01 Jul 2014 02:59:54 GMT

Hang on, I just reread you original question. Are you trying to do this with calculated dimensions? The dimension of the chart, using my expression above, should just be Store Nbr.

Alternatively, if you want to use calculated dimensions, you can created the following calculated dimension:

aggr(

if(rank(sum([YTD POS Sales]),4,1)>=50 and rank(sum([YTD POS Sales]),4,1)<=100,[Store Nbr])

,[Store Nbr]

)

Make sure to check the "Suppress When Value is Null" checkbox. The expression of the chart would just be sum([YTD POS Sales])

Regards,

Vlad

Re: Rank Between Values

Wed, 02 Jul 2014 12:27:08 GMT

Thank you! That worked. Thanks for all your help.

Francesca GomesDirector of Customer Relations

Merchandise Solutions

1100 Burloak Drive, Suite 300

Burlington, ON L7L 6B2

Ph. 905-336-8969

www.merchandise-solutions.com

On Mon, Jun 30, 2014 at 11:00 PM, Vlad Gutkovsky <qcwebmaster@qlikview.com>

Re: Rank Between Values

ThornOfCrowns — Wed, 02 Jul 2014 12:31:44 GMT

Please mark an answer as correct to aid others who may come searching in future.