Solved: Re: Accumulative sums - conditional in aggregation - Qlik Community

Anonymous · ‎2016-03-22

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

agustinbobba · ‎2016-03-22

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

swuehl · ‎2016-03-22

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 · ‎2016-03-22