Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Aggr(sum in calculated dimension

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

4 Replies
Highlighted
MVP
MVP

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)

Highlighted
Champion III
Champion III

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

Highlighted
Champion III
Champion III

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

Highlighted
MVP & Luminary
MVP & Luminary

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