Cumulative count distinct

[maxime66]

Hi qlikers,

I try to calculate a cumulative distinct count on 3 rolling day.

The yellow column.

i've tried a rangesum combinate with above function , but result is a just a non distinct cumulative sum 😞

Thanks by advance.

[edwin · Accepted solution]

you need an association between the dates you want to group as you 3-day spread:

Jan 1 - Jan 1
Jan2 - Jan2
Jan 2 - Jan 1
Jan 3 - Jan 3
Jan 3 - Jan 2
Jan 3 - Jan 1

data:
load * inline [
date,nb_customer
1/1/2020,jack
1/1/2020,jon
1/2/2020,marc
1/3/2020,jack
1/3/2020,mike
1/3/2020,lisa
1/4/2020,marc
1/4/2020,anna
1/5/2020,jessy
1/6/2020,jessy
1/6/2020,lisa
];

Bridge:
load distinct date as keyDate, date  //points to current date
Resident
data;

concatenate(Bridge)
load distinct date as keyDate, date(date-1) as date //prior date
Resident
data;

concatenate(Bridge)
load distinct date as keyDate, date(date-2) as date //3rd date
Resident
data;

the first table shows your data by day, the 2nd shows the cumulative 3-day spread

[maxime66]

Hi Edwin,

Thank you for this solution , works fine !

Is there a solution with an expression to solve this issue as well ?