count of distict agaist distinct of sum

[#1]

Hi,

Here is my problem, I can get to this. So for each email id I can find how many times a marketing email was sent.

Recipient Email	Sum([Sent ?])
a@123.com	54
b@123.com	54
c@123.com	68
d@123.com	73
e@123.com	73
f@123.com	73

But I want to achieve below in Qlikview,

Sum of Sent	Count of Distinct Email
54	2
68	1
73	3

How to achieve this in Qlikview?

Data is stored on grain as

Receipient Email                Sent ?

a@123.com                          1

a@123.com                          2

and so on. so Sum(Sent?) comes out to be 54 for a@123.com

Thanks,

[CELAMBARASAN]

Hi,

     Do this in script itself like

     Load

          ReceipentEmail,

          Sum([Sent?]) AS MailSent

     RESIDENT

          TableName

     GROUP BY ReceipentEmail.

Celambarasan

[#3]

Yes, I agree. That would require a change in the scripts. I want to avoid that.

Is there a way without script change? Assume, This is to be done by a User who doesn't have access to the edit script. (Sorry, I should have mentioned it in my initial question)

[CELAMBARASAN · Accepted solution]

Hi,

     If you need to do it in chart

     use Dimension as Aggr(Sum([Sent?]), ReceipentEmail)

     use Expression as Count(DISTINCT ReceipentEmail)

Celambarasan

[#5]

Thank you so much, Celambarasan!!

I was going in loops on Aggr() function but wasn't able to nail it down. I read about this function in Qlikview Designer II book that was published for QV 10.

Can you please explain me this function in a simple way and better way? Or at least provide me a link to read about this in detail. It's still a little confusing to me.