topic Re: Sum AGGR across multiple fields with Set Expression in QlikView

Sum AGGR across multiple fields with Set Expression

Anonymous — Fri, 17 Nov 2017 04:21:24 GMT

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.

Re: Sum AGGR across multiple fields with Set Expression

jonathandienst — Fri, 17 Nov 2017 04:56:11 GMT

Try using Month and this Aggr() as dimensions:

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

Re: Sum AGGR across multiple fields with Set Expression

stabben23 — Fri, 17 Nov 2017 07:32:59 GMT

Hi,

you could also use this Expression:

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

Re: Sum AGGR across multiple fields with Set Expression

Anonymous — Fri, 17 Nov 2017 09:53:50 GMT

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!

Re: Sum AGGR across multiple fields with Set Expression

Anonymous — Fri, 17 Nov 2017 09:55:15 GMT

Hi Staffan,

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