topic Re: Sum distinct CustomerID in QlikView

Sum distinct CustomerID

Mon, 14 Sep 2015 09:23:35 GMT

Hi All,

i am sitting with a requirement to write:

the sum of Revenue where Customer ID is equal to Distinct

but have no idea how to tackle this one, any help would be accepted.

Thanks.

Re: Sum distinct CustomerID

sunny_talwar — Mon, 14 Sep 2015 09:29:05 GMT

Would you be able to share some data to see what you are trying to accomplish?

Re: Sum distinct CustomerID

prma7799 — Mon, 14 Sep 2015 09:30:53 GMT

What you actually want. Can you elaborate more?

Re: Sum distinct CustomerID

avinashelite — Mon, 14 Sep 2015 09:32:40 GMT

may be like this:

if (count(Customer ID)=1,sum(Revenue ))

if this doesn't give the desired result , then you need check the count in the SQL i.e in the script level and set a flag for the same. Finally based on the flag value you can calculate the sum

Re: Sum distinct CustomerID

tresB — Mon, 14 Sep 2015 09:36:07 GMT

May be like:

Sum({<[Customer ID={"=Count([Customer ID])=1"}]>}Revenue)

Re: Sum distinct CustomerID

Anonymous — Mon, 14 Sep 2015 09:41:31 GMT

maybe:

sum(aggr(sum(DISTINCT Revenue), Customer ID))

Re: Sum distinct CustomerID

Mon, 14 Sep 2015 09:44:19 GMT

Hi All,

im trying to implement most Solutions you guys are giving me,

one more thing that i want is: Distinct CustID and Number of Revenues made each distinct CustID Made.

Thanks.

Re: Sum distinct CustomerID

sunny_talwar — Mon, 14 Sep 2015 09:47:25 GMT

Alternative to this could be:

=Sum(Aggr(Only(Revenue), CustID)) or

=Sum(Aggr(Avg(Revenue), CustID))

Re: Sum distinct CustomerID

avinashelite — Mon, 14 Sep 2015 09:49:37 GMT

yes, count(Customer ID)=1 will give you you the Distinct custID....once this condition is satisfied..for the distinct Custid you will get the sum

Re: Sum distinct CustomerID

tresB — Mon, 14 Sep 2015 09:52:36 GMT

Try:

Straight table:

Dim: [Customer ID]

Exp: Sum({<[Customer ID={"=Count([Customer ID])=1"}]>}Revenue)

Re: Sum distinct CustomerID

nagarjuna_kotha — Mon, 14 Sep 2015 10:01:23 GMT

PFA....

Re: Sum distinct CustomerID

nagarjuna_kotha — Mon, 14 Sep 2015 10:04:28 GMT

Check Script first and apply same in your script if it is possible .