Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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...