Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
Yes, one "aggr()" is cleaner of course. But we still have to count our '(' and ')'
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