Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

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
Highlighted
MVP
MVP

Re: Sum AGGR across multiple fields with Set Expression

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
Highlighted
MVP
MVP

Re: Sum AGGR across multiple fields with Set Expression

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

Highlighted
Honored Contributor

Re: Sum AGGR across multiple fields with Set Expression

Hi,

you could also use this Expression:

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

Highlighted
New Contributor III

Re: Sum AGGR across multiple fields with Set Expression

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!

Highlighted
New Contributor III

Re: Sum AGGR across multiple fields with Set Expression

Hi Staffan,

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