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!

[sunny_talwar]

May be this:

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

[samuel_lin]

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

[sunny_talwar]

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

[samuel_lin]

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.

[sunny_talwar]

May bad, try adding a DISTINCT

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

[jyothish8807]

TRy like this:

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

Regards

KC

[samuel_lin]

Hi Jyothish,

unfortunately that dashes out the outcome.

[samuel_lin]

Hey Sunny,

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

[jyothish8807 · Accepted solution]

What about this:

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