topic Re: Stratification with Rank(Aggr()) as a dimension in QlikView

Stratification with Rank(Aggr()) as a dimension

Wed, 29 Jan 2014 23:33:24 GMT

Hi all,

I'm trying to create a dimension that is a stratification of items by spend. I want to bucket the first 150 items, ordered by spend, in bucket '1-150', the next 150 items in bucket '151-300', and so on. I tried a few variations of the below formula in the dimension but nothing is working:

=if(Rank(aggr(sum(TotalPrice), Item)) <=150, '1-150',

if(Rank(aggr(sum(TotalPrice), Item)) <=300, '151-300',

if(Rank(aggr(sum(TotalPrice), Item)) <=600, '301-600', '600+')))

I'm getting the error message "Allocated memory exceeded." Can anyone help me build the strat?

Thanks

Re: Stratification with Rank(Aggr()) as a dimension

Anonymous — Wed, 29 Jan 2014 23:52:43 GMT

class( rank(aggr(sum(TotalPrice), Item)) ,150)

Re: Stratification with Rank(Aggr()) as a dimension

Carlos_Reyes — Thu, 30 Jan 2014 00:02:45 GMT

DUAL(

AGGR(

IF(RANK(sum(TotalPrice))<=150,'1-150',

IF(RANK(sum(TotalPrice))<=300,'151-300',

IF(RANK(sum(TotalPrice))<=600,'301-600','600+'

)))

,Item)

AGGR(

IF(RANK(sum(TotalPrice))<=150,1,

IF(RANK(sum(TotalPrice))<=300,2,

IF(RANK(sum(TotalPrice))<=600,3,4

)))

,Item)

)

Re: Stratification with Rank(Aggr()) as a dimension

Thu, 30 Jan 2014 15:39:39 GMT

Yes! This works perfectly. Do you mind explaining what the Dual() function is doing to each aggr() function?

Re: Stratification with Rank(Aggr()) as a dimension

Anonymous — Thu, 30 Jan 2014 15:45:29 GMT

The first half of the equations is evaluating to text, so when you display the dimension in a chart it will not show in the order you want. Dual assigns a # value to each text i.e '1-150' = 1 so you can sort the values in a chart numerically and get the order you want.

Re: Stratification with Rank(Aggr()) as a dimension

Carlos_Reyes — Thu, 30 Jan 2014 15:47:33 GMT

Couldn't explain better than Michael...

Re: Stratification with Rank(Aggr()) as a dimension

Thu, 30 Jan 2014 15:56:44 GMT

Well this is slick, and I should have realized just from looking at the equation. I usually use an inline load for that, but this is much better! Thanks for the explanation.