Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi, you can try with:
count(distinct CustomerID)/Avg(TOTAL Aggr(count(distinct CustomerID), YearMonth))
Hi, you can try with:
count(distinct CustomerID)/Avg(TOTAL Aggr(count(distinct CustomerID), YearMonth))
count(DISTINCT Customer)
/
(count(TOTAL DISTINCT Customer)/count(TOTAL DISTINCT YearMonth))
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!