Sum AGGR across multiple fields with Set Expression

[Anonymous]

Hi,

I have a simple table as below:

Salesman	Product	Cost	Month
Richard	Bus	500	JAN
Johan	Car	200	JAN
Johan	Bus	700	JAN
Leroy	Boat	800	JAN
Leroy	Plane	15000	FEB
Samuel	Car	300	FEB
Richard	Car	2000	MAR
Samuel	Plane	10000	MAR
Leroy	Car	160	APR

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) )
JAN	Leroy	800
FEB	Leroy	15000
MAR	Richard	2000
MAR	Samuel	10000

I am expecting ...

JAN	Leroy	800
JAN	Johan	900
FEB	Leroy	15000
MAR	Richard	2000
MAR	Samuel	10000

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.

[jonathandienst · Accepted solution]

Try using Month and this Aggr() as dimensions:

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

[stabben23]

Hi,

you could also use this Expression:

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

[Anonymous]

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]

Hi Staffan,

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