both inner and outer set analysis with Aggr

[samuel_lin]

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?

Thank you for your help!

[samuel_lin]

Hey Sunny,

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

it works when i apply Jyothish second response below:

count(distinct if(Aggr(sum({<dimension1={'AAA', 'BBB'}, dimension2={'XXX'}>} Revenue), EmailAddress)>1000,EmailAddres)) AND I have to add Dimension3 for aggr before emailAddress like:

count(distinct if(Aggr(sum({<dimension1={'AAA', 'BBB'}, dimension2={'XXX'}>} Revenue), [Dimension3], EmailAddress)>1000,EmailAddres))

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!!

[sunny_talwar]

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:

AutoNumber(Dimension3, EmailAddress) as Dim3_Email_Key

and then this might work

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