Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
samuel_lin
Not applicable

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?

Thank you for your help!

Tags (2)
1 Solution

Accepted Solutions
jyothish8807
Not applicable

Re: both inner and outer set analysis with Aggr

What about this:


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

Best Regards,
KC
11 Replies
sunny_talwar
Not applicable

Re: both inner and outer set analysis with Aggr

May be this:

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

samuel_lin
Not applicable

Re: both inner and outer set analysis with Aggr

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

sunny_talwar
Not applicable

Re: both inner and outer set analysis with Aggr

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

samuel_lin
Not applicable

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.

sunny_talwar
Not applicable

Re: both inner and outer set analysis with Aggr

May bad, try adding a DISTINCT

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

jyothish8807
Not applicable

Re: both inner and outer set analysis with Aggr

TRy like this:

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

Regards

KC

Best Regards,
KC
samuel_lin
Not applicable

Re: both inner and outer set analysis with Aggr

Hi Jyothish,

unfortunately that dashes out the outcome.

samuel_lin
Not applicable

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

jyothish8807
Not applicable

Re: both inner and outer set analysis with Aggr

What about this:


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

Best Regards,
KC