Aggr(sum in calculated dimension

[Anonymous]

Hi all,

I need some help with this calculated dimension.

I have a calculated field that has values between [0; 1000000] and need to create a calculated dimension rate this range in 5 ranges:

Sales           Inputfield (Discont%)

<= 5000                   -

<=10000                  -

<=15000                  -

<=20000                  -

<=25000                  -

>250000                  -

I use this expression in calculated dimension:

=if(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)<=5000,Dual('5000',0),

     if(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)<=10000,Dual('10000',5000),

     if(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)<=15000,Dual('15000',10000),  

     if(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)<=20000,Dual('20000',15000),

     if(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)<=25000,Dual('25000',20000), dual( '>25000',25000)))))

But don´t work. Any idea?

Thank in advance,

Pedro Lopes

[MK_QSL]

Aggr(

     IF(SUM((PrecoSimu2 * (1-Order_Matrix)) * Unit_base_demand) <= 5000, Dual('5000',0),

     IF(SUM((PrecoSimu2 * (1-Order_Matrix)) * Unit_base_demand) <= 10000, Dual('10000',1)....and so on

     ),id_cliente)

[Anonymous]

I suspect it should work as is if you only add one ')' at the end.  I tried your calculated dimension in the "Sample script", see attched

[Anonymous]

Yes, one "aggr()" is cleaner of course.  But we still have to count our '(' and ')'

[marcus_sommer]

As alternatively to your nested if-loop (which could be very slow in larger apps) you could try to cluster your data with class() or you used a kind of lookup-function with pick(match()) like:

dual(

pick(match(ceil(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)/5000), 1, 2, 3, …), '<= 5000', '<= 10000', …),

match(ceil(aggr(sum((PrecoSimu2*(1-Order_Matrix))*Unit_base_demand),id_cliente)/5000), 1, 2, 3, …))

- Marcus