topic How to do set analysis in this Aggr Sum expression in QlikView

How to do set analysis in this Aggr Sum expression

Mon, 22 Aug 2016 05:32:41 GMT

Hi Guys,

I am using following Expression in text box to show sum of total paid amount for orders.

=sum(Aggr([Paid Amount], OrderId,[Paid Amount]))

Where Can I add 'if' statement or set analysis in this expression to only see total amount where [Sales Rep] = 'James' ?

stephenedberkg — Mon, 22 Aug 2016 05:35:41 GMT

try like this

aggr( sum({< [Sales Rep] = {'James'} >}[Paid Amount])OrderId)

Mon, 22 Aug 2016 05:39:46 GMT

Thanks Stephen, but did not work

NareshGuntur — Mon, 22 Aug 2016 05:53:02 GMT

I don't think you need to use Aggr function for that.

The below should do -

sum({< [Sales Rep] = {'James'} >}[Paid Amount])

tamilarasu — Mon, 22 Aug 2016 06:15:13 GMT

May be this.

=sum(Aggr(If([Sales Rep] = 'James',[Paid Amount]), OrderId,[Paid Amount]))

=sum(Aggr(Sum({<[Sales Rep] = {'James'}>}[Paid Amount]), OrderId,[Paid Amount]))

sunny_talwar — Mon, 22 Aug 2016 10:12:11 GMT

Not sure how and where you using this, but may be this (if what Naresh provided doesn't work for you)

=Sum({<[Sales Rep] = {'James'}>}Aggr(Only({<[Sales Rep] = {'James'}>}[Paid Amount]), OrderId,[Paid Amount]))

Tue, 23 Aug 2016 01:01:28 GMT

Thanks for your replies guys..

Almost everyone's expression worked.. but I picked Tamil's for now

Thanks