Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi Edwin,
Thank you for this solution , works fine !
Is there a solution with an expression to solve this issue as well ?