topic Aggr(sum in calculated dimension in QlikView

Aggr(sum in calculated dimension

Anonymous — Fri, 10 Apr 2015 16:52:28 GMT

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

Re: Aggr(sum in calculated dimension

MK_QSL — Fri, 10 Apr 2015 17:17:18 GMT

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)

Re: Aggr(sum in calculated dimension

Anonymous — Fri, 10 Apr 2015 17:19:30 GMT

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

Re: Aggr(sum in calculated dimension

Anonymous — Fri, 10 Apr 2015 17:23:10 GMT

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

Re: Aggr(sum in calculated dimension

marcus_sommer — Sun, 12 Apr 2015 15:23:40 GMT

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