topic Re: Cumulative count distinct in QlikView

Cumulative count distinct

maxime66 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cumulative count distinct

edwin — Tue, 27 Oct 2020 11:19:41 GMT

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

Re: Cumulative count distinct

maxime66 — Tue, 27 Oct 2020 20:08:38 GMT

Hi Edwin,

Thank you for this solution , works fine !

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