topic Re: How To Sum Distinct Dimension? in QlikView

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 01:53:55 GMT

Hello,

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

CustomerID CLICKS POS

1901 8 US

1901 8 JP

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!

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 03:48:05 GMT

1. What is the expected result if there is one more entry? e.g. 1901 9 US.

2. Check out the distinct keyword.

How To Sum Distinct Dimension?

SunilChauhan — Thu, 10 Nov 2011 05:56:30 GMT

customer ID in dimension and

avg(CLICKS )

aggr(avg(CLICKS ),CutomerId)

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 06:19:18 GMT

Pls try this ...

=sum(DISTINCT Clikcs)

Regards,

Antony.

How To Sum Distinct Dimension?

swuehl — Thu, 10 Nov 2011 12:48:36 GMT

I would suggest

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

This will show the same results as sum(aggr( avg(CLICKS),CustomerID)) or sum(aggr( max(CLICKS),CustomerID)) etc. with above sample data, but different if CustomerID might have multiple distinct CLICKS, like vishal_pai mentioned.

I believe sum(distinct CLICKS) will only work for distinct CLICKS across CustomerID, so if both customers would have 5 CLICKS on each record, I think the sum should be 10, not 5.

Hope this helps,

Stefan

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 17:47:24 GMT

That's a very good point if a CustomerID has two or more distinct CLICKS (8 & 9).

I'm ok with simply taking the maximum (9), but definitely NOT (cannot be 8+9). Basically a group by on CustomerID, and return one value only and then sum them up.

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

I would assume, above is preferred expression as supposed to

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

Correct me if I am wrong. Appreciate your help!

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 18:19:39 GMT

1. Is your data set result of a join that cannot be avoided? E.g. Same customer id having same number of clicks across different POS does not look right.

2. Can you please define what you are trying to achieve? E.g. why are you ok with max clicks.

The reason I am asking these questions is following:

Although an expression might solve your short term problem for now it will not help when you are solving the bigger picture. E.g. Qlikview is powerful because selecting data in a chart / table et al updates the other views. And that is necessary to discover useful data. If your data model is not right and consistent, you will face a lot of problems going forward.

I will reply to the expressions part tomorrow.

Best Regards,
Vishal

How To Sum Distinct Dimension?

Thu, 10 Nov 2011 18:46:13 GMT

I provided the sample dataset above, but actually, my final report is group by POS.

Dataset:

CustomerID CLICKS POS

1901 8 US

1901 8 JP

2901 5 US

Desired output looks something as such:

POS SUM AVG

US 8+5 (8+5)/2 = 6.5 (2 is distinct CustomerID for this POS)

JP 8 8/1 = 8

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))

Vishal, yes, you brought up a very good point, because I am joining on different granularity. The dataset is at placement level, but the CLICKS data is only at CustomerID level, so that's why there is repeated CLICKS in my dataset. Would you guys recommend breaking out the CLICKS as a separate dataset, and let QV join simply on CustomerID & POS?

Your help is appreciated!

Re: How To Sum Distinct Dimension?

Fri, 11 Nov 2011 00:22:02 GMT

I've attached the QVW. I don't think aggr is summing correctly for A2 & A3 Bucket, also how to calculate avg?

FYI, I added Bucket to my dataset.

Dataset:

CustomerID CLICKS POS Bucket

1901 8 US A1

1901 8 US A2

1901 8 JP A2

2901 5 US A2

2901 5 US A3

Desired output looks something as such:

Bucket SUM AVG

A1 8 8 / 1 = 8

A2 8+5 = 13 (8+5)/2 = 6.5 (2 is distinct CustomerID for this Bucket)

A3 5 5 / 1 = 5

Re: How To Sum Distinct Dimension?

Fri, 11 Nov 2011 03:59:53 GMT

1. Dimension Bucket and expression Avg(DISTINCT CLICKS) (Edit: the other expression is Sum(DISTINCT CLICKS)). But I am really not sure if it will solve all your problems going forward wrt consistency of the solution.

2. Regarding data re-modelling...I am not yet there to comment on it . From my reading on the community and blogs there is no one right solution. It depends on your situation. Check this one out. http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

3. Less is always better. Every time I need to add a column to the dataset I worry because it might result in data set being inconsistent in different situations.

Best Regards,

Vishal

How To Sum Distinct Dimension?

qliksus — Fri, 11 Nov 2011 09:19:41 GMT

Use some thing like this

avg(aggr(Clicks,Bucket))

Re: How To Sum Distinct Dimension?

johnw — Sat, 12 Nov 2011 01:38:44 GMT

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.

Re: How To Sum Distinct Dimension?

Mon, 24 Sep 2012 15:00:18 GMT

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!

Re: How To Sum Distinct Dimension?

psankepalli — Fri, 31 Mar 2017 18:25:58 GMT

Stefan,

This answer is very helpful! thank you

- San

Re: How To Sum Distinct Dimension?

wonkymeister — Thu, 18 Oct 2018 11:48:12 GMT

awesome - thank you!

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

Re: How To Sum Distinct Dimension?

Anonymous — Thu, 18 Oct 2018 12:20:45 GMT

you can also use this expression in backend:

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