Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum AGGR across multiple fields with Set Expression

Hi,

I have a simple table as below:

Salesman Product Cost Month
RichardBus500JAN
JohanCar200JAN
JohanBus700JAN
LeroyBoat800JAN
LeroyPlane15000FEB
SamuelCar300FEB
RichardCar2000MAR
SamuelPlane10000MAR
LeroyCar160APR

I'm trying to calc the Cost per Salesman per Month, and filter out only Salesman in a Month with > 800 Cost

In my chart, I'm using

Dimensions:

Salesman

Month

Expressions:

=sum( aggr( sum({<Cost={'>= $(vAmtThreshold)'}>}Cost),Salesman,Month))

where vAmtThreshold = 800

However, my results show up as

Month Salesman =sum( aggr( sum({<Cost={'>= 800'}>}Cost),Salesman,Month) )
JANLeroy800
FEBLeroy15000
MARRichard2000
MARSamuel10000

I am expecting ...

JANLeroy800
JANJohan900
FEBLeroy15000
MARRichard2000
MARSamuel10000

So Johan / 900 / JAN is missing from my data set ... How can I add the total Costs across Salesman/Month in this case?

I've tried putting the AGGR as a Calc dimension and as an Expression... Both are still achieving the same output

Is there something I'm missing?

Thanks.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Try using Month and this Aggr() as dimensions:

=Aggr(If(Sum(TOTAL <Salesman> Cost) >= $(vAmtThreshold), Salesman), Salesman, Month)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Try using Month and this Aggr() as dimensions:

=Aggr(If(Sum(TOTAL <Salesman> Cost) >= $(vAmtThreshold), Salesman), Salesman, Month)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
stabben23
Partner - Master
Partner - Master

Hi,

you could also use this Expression:

=sum(aggr(if(sum(total <Salesman,Month> Cost)>=$(vAmtThreshold), Cost),Salesman,Month,Product))

Anonymous
Not applicable
Author

Thanks Johnathan! I tried both suggested solutions and both worked! I selected using Expressions as I thought it might be more efficient in terms of memory management, although I did start off my logic using Calc Dimensions...

Thank you!

Anonymous
Not applicable
Author

Hi Staffan,

Great! Thanks! This was eluding me for a few days...