Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!!
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)