11 Replies Latest reply: Nov 3, 2016 2:46 PM by Sunny Talwar

# both inner and outer set analysis with Aggr

Hello Qlikers!!

I have the following expression, but the result isn't correct....

Count({<Revenue={'>1000'}>} Aggr(sum({<dimension1={'AAA', 'BBB'}, dimension2={'XXX'}>} Revenue), EmailAddress))

basically first I want Aggr(sum({<dimension1={'AAA', 'BBB'}, dimension2={'XXX'}>} Revenue), EmailAddress)

so the sum of revenue by EmailAddress, and then, I would like to count the number of emailAddresses that has revenue greater than say 1000

Should be straight forward?

• ###### Re: both inner and outer set analysis with Aggr

May be this:

• ###### Re: both inner and outer set analysis with Aggr

Hey Sunny, so that actually didn't give the right count either...

• ###### Re: both inner and outer set analysis with Aggr

Have you checked the number of EmailAddress with revenue over 1000 in a straight table?

• ###### Re: both inner and outer set analysis with Aggr

yes, I have; I also checked using the selection boxes/filter panels; just for your reference, the number should be 78, but the number with your expression gives 105.

• ###### Re: both inner and outer set analysis with Aggr

• ###### Re: both inner and outer set analysis with Aggr

Hey Sunny,

I am still not getting it right, i am still trying to figure this out....

• ###### Re: both inner and outer set analysis with Aggr

Hey Sunny,

I think I know the problem and let me try to describe

it works when i apply Jyothish second response below:

Whereas Dimension3 is the dimension that my table is based on. I went through many many thoughts on this and the guess to add dimension3 is apparently correct; the only downside though, running the if statement seems slow. If I want to apply similar thing to your expression (adding Dimension3), how would you implement that?

Thank you!!

• ###### Re: both inner and outer set analysis with Aggr

Unfortunately, if you need to add Dimension3 in there, then set analysis won't work anymore unless you are able to create a new field in your script, something like this:

and then this might work

Count(DISTINCT {<Dim3_Email_Key = {"=Sum({<dimension1={'AAA', 'BBB'}, dimension2={'XXX'}>} Revenue) > 1000"}>} Dim3_Email_Key)

• ###### Re: both inner and outer set analysis with Aggr

TRy like this:

Regards

KC

• ###### Re: both inner and outer set analysis with Aggr

Hi Jyothish,

unfortunately that dashes out the outcome.