How To Sum Distinct Dimension?

[#1]

Hello,

I have a simple dataset below.  How do I sum only the distinct CustomerID?

CustomerID  CLICKS  POS

1901            8            US

1901            8            US

1901            8            JP

2901            5            US

2901            5            US

Current Sum 8+8+8+5+5 = 34

Desire Sum 8+5 = 13

Do I use a aggr function to group by CustomerID, and take the avg of the clicks?  (8+8+8)/3=8

What would the expression look like?

Your help is much appreciated!

[qliksus]

Use some thing like this

avg(aggr(Clicks,Bucket))

[johnw]

captainlaw wrote:

I tested the expression below, but is not returning the correct sum.  Do I need to aggr by POS as well?

=sum(aggr( max(CLICKS),CustomerID))

Would you guys recommend breaking out the CLICKS as a separate dataset, and let QV join simply on CustomerID & POS?

With the current data model, yes, you just need to aggregate by POS as well.  For your sample file, use these expressions:

sum(aggr(max(CLICKS),CustomerID,Bucket))
avg(aggr(max(CLICKS),CustomerID,Bucket))

I'm unclear what your example data actually means, so can't advise well on the correct data model.  But it seems likely that if clicks are only associated with CustomerID, then that should be a separate table.

[#4]

Hi captainlaw,

If I got it correctly, you would like to group by CusomerID and the sum the corresponding clicks. Let's build the function in two steps:

Step 1: group by customer ID while keeping their clicks: aggr(clicks,CustomerID). Internally this is treated as a table like:

Customer ID   Clicks

1901                  8

2901                  5

Step 2: sum the values: sum(aggr(clicks,CustomerID))

Cheers!

[psankepalli]

Stefan,

This answer is very helpful! thank you

- San

[wonkymeister]

awesome - thank you!

really, really, really need to start using aggr more!

[Anonymous]

you can also use this expression in backend:

=if(count(Customer ID)>1, sum(distinct (Clicks), sum(Clicks))