Make by month with 2 dates (start & end)

[Max1]

Hello 😊

I'm starting to make something a bit easy with another language but with qlik I don't find how.

With this table by ID :

ID	date start	date end	end - start => number of day TOTAL ID
A	2020-01-31	2020-02-02	3

How can I do in script or set analysis (or both solutions to compare ^^!) something like this (to make a graphic by month cumulated) :

ID	Period BY MONTH	number of day TOTAL ID	number of day BY MONTH	number of day BY MONTH CUMULATED
A	2020-01	3	1	1
A	2020-02	3	2	3

Thanks for reading.

[QFabian]

Hi, @Max1  try this:

Date:
LOAD * INLINE [
ID,Start, End
A,2020-01-31, 2020-02-02
];

Result:
Load
ID,
Start,
End,
End-Start+1 as NumberOfDayTotalID
Resident Date;
drop table Date;

Let vStart = num(peek('Start',0, 'Date2'));
Let vEnd = num(peek('End',0, 'Date2'));
Let vDays = num(peek('NumberOfDayTotalID',0, 'Date2'));
Let vID = peek('ID',0, 'Date2');

Dates:
Load
'$(vID)' as ID,
monthname(if(rowno()=1, $(vStart), peek(Date)+1)) as Period,
if(rowno()=1, $(vStart), peek(Date)+1) as Date
autogenerate($(vDays));

Month:
Load
ID,
Period,
count(Date) as NumberOfDay
Resident Dates
group By ID, Period;
drop table Dates;

LEFT join (Result)
Load
*,
if(rowno()=1, NumberOfDay, NumberOfDay + peek(NumberOfDayCumulated)) as NumberOfDayCumulated
Resident Month;
drop table Month;

//This will solve just one ID .....

[Taoufiq_Zarra · Accepted solution]

@Max1  if you have multiple ID you can try below :

Data:

load *,[date end]-[date start]+1 as [number of day TOTAL ID];
LOAD ID, Date#([date start],'YYYY-MM-DD') as [date start], Date#([date end],'YYYY-MM-DD') as [date end] INLINE [
    ID, date start, date end
    A, 2020-01-31, 2020-02-02
];
left join
load ID ,Date(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TempDate,Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TmpMonth,Year(Date#([date start],'YYYY-MM-DD') + IterNo() - 1)&'-'&Num(Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1),'00') as [Period BY MONTH] resident Data  
While Date#([date start],'YYYY-MM-DD') + IterNo() -1 <=  Date#([date end],'YYYY-MM-DD');

left join load ID,TmpMonth,count(TempDate) as [number of day BY MONTH] resident Data group by ID,TmpMonth;


Tmp:
noconcatenate
load distinct ID,[Period BY MONTH],[number of day TOTAL ID],[number of day BY MONTH] resident Data;

drop table Data;

output:
noconcatenate

load *,if(peek(ID)=ID,peek([number of day BY MONTH] )+[number of day BY MONTH] ,1) as [number of day BY MONTH CUMULATED] resident Tmp;

drop table Tmp;

output:

to change Inline [...] by your load form database or file...

[Max1]

Hi @Taoufiq_Zarra  and @QFabian

Thank you very much for your help, I have choose the 2nd solution (wich work with multiple ID) 🙂

I have just change some things like the left joins by noconcatenate and no distinct, I understand a bit more with this but I the same result.

And just add "CUMULATED" in peek 😉

load *,if(peek(ID)=ID,peek([number of day BY MONTH] )+[number of day BY MONTH CUMULATED] ,1) as [number of day BY MONTH CUMULATED] resident Tmp;

I'm trying now to ajust performance because I have a lot of line in source. Make one line by day take a long time.

Have a good day.