Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How To Sum Distinct Dimension?

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!

15 Replies
qliksus
Specialist II
Specialist II

Use some thing like this

avg(aggr(Clicks,Bucket))

johnw
Champion III
Champion III

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.

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

Stefan,

This answer is very helpful! thank you

- San

wonkymeister
Creator III
Creator III

awesome - thank you!

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

Anonymous
Not applicable
Author

you can also use this expression in backend:

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