# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for
Did you mean:
Highlighted
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 ...

 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.

1 Solution

Accepted Solutions
Highlighted

## 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
4 Replies
Highlighted

## 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
Highlighted
Master

## 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
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
Contributor III

## Re: Sum AGGR across multiple fields with Set Expression

Hi Staffan,

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