Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dividing by the average of a distinct count in a straight table

Hello everyone,

It's very easy to construct the following straight table in Qlikview with the dimension YearMonth, expression count(distinct CustomerID) and

I need to add a column which will divide the number of unique customers per month by the average. In Excel this is very simple:

How do I create this column in Qlikview?

1 Solution

Accepted Solutions
rubenmarin

Hi, you can try with:

count(distinct CustomerID)/Avg(TOTAL Aggr(count(distinct CustomerID), YearMonth))

View solution in original post

3 Replies
rubenmarin

Hi, you can try with:

count(distinct CustomerID)/Avg(TOTAL Aggr(count(distinct CustomerID), YearMonth))

maxgro
MVP
MVP

count(DISTINCT Customer)

/

(count(TOTAL DISTINCT Customer)/count(TOTAL DISTINCT YearMonth))

Not applicable
Author

Thanks Ruben and Maxgro.

count(DISTINCT Customer)

/

(count(TOTAL DISTINCT Customer)/count(TOTAL DISTINCT YearMonth))

only works well with count and sum. Since customers are able to purchase in multiple months, doing a count total distinct will count such customers only once each which means that (count(TOTAL DISTINCT Customer)/count(TOTAL DISTINCT YearMonth)) is not actually equal to the average number of customers per month.

Avg(TOTAL Aggr(count(distinct CustomerID), YearMonth)) takes care of this and appears to be the formula which Qlikview uses in calculation when we tick the box below:

- thanks @Ruben!