Rank Between Values

[#1]

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!

[maxgro]

=

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]))

[vgutkovsky]

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

[maxgro]

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

[#5]

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

Thanks again.

[vgutkovsky]

Which version of the expression are you using?

[#7]

The one you provided.

[vgutkovsky]

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

[#9]

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

[vgutkovsky]

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