Distinct diagram cumulation

[Anonymous]

Hello Community,

I want to export an up-to-now manually created report (that my company has used for years) to Qlikview and I'm running into some problems.

The idea is to track the activity of a defined group of customers over a certain time and see how the activity percentage rises over time (here from week to week).

Therefore a span of weeks is defined and the number of unique customers is tracked and cumulated per week.

Example:

3 tracked weeks

9 Total Customers in observed group

6 Customers  bought something (1 is a transaction):

CustomerID	Week 1	Week 2	Week 3
Customer1	1	1	1
Customer2	1	-	1
Customer3	1	-	-
Customer4	-	1	-
Customer5	-	1	-
Customer6	-	-	1

So for example customer 1 is active in all 3 weeks

and customer 3 only in week 1.

Therefore the counted unique customers (6 of 9 bought once or more in the 3 weeks) would be in each week:

Week	Total	Week 1	Week 2	Week 3
count(distinct CustomerID)	6	3	3	3
count( {<Week=>} total distinct CustomerID)	9	9	9	9

When these are cumulated over time I get (complete accumulation in the formula tab):

Week	Total	Week 1	Week 2	Week 3
count(distinct CustomerID)	6	3	6	9
count( {<Week=>} total distinct CustomerID)	9	9	9	9
Activity Percentage		33%	67%	100%

But that's not what I want because customers who bought in 2 or more weeks get counted once for every week they bought something in.

The right cumulation without double entries should result in:

Week	Total	Week 1	Week 2	Week 3
right cumulation	6	3	5	6
=count( {<Week=>} total distinct CustomerID)	9	9	9	9
Activity Percentage		33%	56%	67%

But that is wrong, because actually only 6 out of 9 bought something, but the cumulation just adds up the numbers of each week and therefore, because some people buy something in 2 or more weeks, the activity percentage is too high in the end.

I understand that Qlikview treats the DISTINCT per customer per week and not per customer in total, but I didn't find a solution yet.

So is there any way to only cumulate up to the real distinct number over the weeks (here 6) without double counting customers who bought multiple times over these weeks?

Thank you very much in advance!

[sunny_talwar · Accepted solution]

Use AsOfTable approach

Sales:

Load * Inline [

"Week"|"Sales"|"CustomerID"

1|1|Customer1

1|1|Customer2

1|1|Customer3

2|1|Customer4

2|1|Customer5

2|1|Customer1

3|1|Customer6

3|1|Customer1

3|1|Customer2

] (delimiter is '|');

MinWeek:

LOAD Min(Week) as MinWeek

Resident Sales;

LET vMinWeek = Peek('MinWeek');

DROP Table MinWeek;

Customers:

NoConcatenate

Load * Inline [

"CustomerID"

Customer1

Customer2

Customer3

Customer4

Customer5

Customer6

Customer7

Customer8

Customer9

];

AsOfWeek:

LOAD DISTINCT Week as AsOfWeek,

(Week - IterNo() + 1) as Week

Resident Sales

While (Week - IterNo() + 1) >= $(vMinWeek);

Dimension:

AsOfWeek

Expression

count(distinct CustomerID)

Check 'No Accumulation' instead of 'Full Accumulation'

[Anonymous]

Seems to work fine, thank you