Stratification with Rank(Aggr()) as a dimension

[#1]

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

[Anonymous]

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

[Carlos_Reyes · Accepted solution]

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)

)

[#4]

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

[Anonymous]

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.

[Carlos_Reyes]

Couldn't explain better than Michael...

[#7]

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.