topic Accumulative sums - conditional in aggregation in QlikView

Accumulative sums - conditional in aggregation

Anonymous — Tue, 22 Mar 2016 14:14:17 GMT

Hi,

the function i have from

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Store))

I have 3 different listobox: Black Store, White Store, Red Store with values attached to them.

I want to have sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Black)) when i select one value from Black Store listbox.

i tried:

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month,

if(getselectedcount(red)>0,red,

if(getselectedcount(white)>0,white,

if(getselectedcount(black)>0,black)) but it doesnt work.

So my question is how can i put only names of columns to aggregation funtion?

Thank You in advance,

Jacek Antek

swuehl — Tue, 22 Mar 2016 15:15:33 GMT

You can only use field names as dimensions in the aggr() function, no functions allowed.

But you can use a dollar sign expansion to evaluate your condition and replace with the output before the expression actually gets parsed:

sum(

aggr(

rangesum( above( sum(Sales),0,12) )

,Month,

$(=if(getselectedcount(red)>0,'red', if(getselectedcount(white)>0,'white', if(getselectedcount(black)>0,'black'))))

)

edit:

Remember: red, white and black need to be fields in your data model, and QV is case sensitiv

agustinbobba — Tue, 22 Mar 2016 15:29:06 GMT

Hi Jacek,

Maybe i misunderstood but i tried to recreate your scenery.

First you cant use the name of the column for the aggregarion funtion only values of a field.

Here I attach you an example, maybe can help you to make some changes on your model.

Best regards,

Agustin

Anonymous — Wed, 23 Mar 2016 07:16:57 GMT

Thank You Guys!

It is working thanks to yours solution, Im happy 😉