Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Cumulative count distinct

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.

 

 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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;

edwin_0-1603797549547.png

 

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

View solution in original post

2 Replies
Highlighted
Creator III
Creator III

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;

edwin_0-1603797549547.png

 

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

View solution in original post

Highlighted
Creator
Creator

Hi Edwin,

Thank you for this solution , works fine !

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