Identify consecutive dates when give a series of dates

[ninawang99]

Hi All,

I got issue to check the number of consecutive dates.

In my dataset, Name and [Leave Date] are dimensions

the result should be like

Name	Blocks
John	2
Kerry	2

Thanks in advance!

[vinieme12 · Accepted solution]

as below

IN Charts use

Dimension:  Name

Expression = Count(Distinct blockKey) 

temp:
load  Name,date(date#(Date,'DD/MM/YYYY')) as edate inline [
Name,Date
ABC,02/03/2023
ABC,03/03/2023
ABC,04/03/2023
ABC,05/03/2023
ABC,06/03/2023
ABC,07/03/2023
ABC,08/03/2023
ABC,09/03/2023
ABC,05/04/2023
ABC,06/04/2023
ABC,07/04/2023
ABC,08/04/2023
ABC,09/04/2023
];

Main:
load Name,edate,if(Name=Peek('Name'),if((edate-peek('edate'))=1,peek('blockKey'),Name&edate),Name&edate) as blockKey
Resident temp
ORder by Name,edate ASC;
drop table temp;
exit Script;

In

[ninawang99]

It worked! Appreciate!!!

[vinieme12]

👍, please mark it as a solution and close the thread