Count Months Open from Date Submitted Qlikview

[Anonymous]

Hi All,

I have a start date and it is in the format 'MMM-YY' I want to be able to display every month it has been open for until its close date which is in the same format 'MMM-YY'. And if it dose not have a close date display every month including the current month as of today.

The data should look like the below if possible:

ID          Start Date          Close Date          Opened

1            Aug-18               Oct-18               Sep-18

2            Jun-18               Oct-18               Jul-18,Aug-18,Sep-18

3            Jul-18                   -                     Aug-18,Sep-18,Oct-18

Any help would be great thanks.                                          

[Jesh19]

Check the below link.

MonthName between Start Date and End Date

[andrey_krylov · Accepted solution]

Hi Ruth. I think it may be like this

Table: LOAD ID, [Start Date], [Close Date],

  Date(Date#([Start Date], 'MMM-YY')) as [StartDate],

  Date(If(Len([Close Date])=0,  MonthStart(Today(), 1), Date#([Close Date], 'MMM-YY'))) as [EndDate];

LOAD * INLINE [

ID,          Start Date,          Close Date

1,            Aug-18,             Oct-18

2,            Jun-18,             Oct-18

3 ,           Jul-18,           

];

Temp_1: LOAD Min([StartDate]) as MinDate Resident Table; vMinDate = Num(Peek('MinDate', 0, 'Temp_1'));

Temp_2: LOAD MonthStart($(vMinDate), IterNo() - 1) as [Date] AutoGenerate 1 While MonthStart($(vMinDate), IterNo() - 1) <= MonthStart(Today(), 1);

Inner Join(Temp_2) IntervalMatch (Date) LOAD Distinct [StartDate], [EndDate] Resident [Table];

Temp_3: LOAD [StartDate], [EndDate], Concat(Date([Date], 'MMM-YY'), ', ', Date) as [Opened] Resident Temp_2 Where not Match([Date], [StartDate], [EndDate]) Group By [StartDate], [EndDate];

Left Join(Table) LOAD * Resident Temp_3; DROP Tables Temp_1, Temp_2, Temp_3; DROP Fields [StartDate], [EndDate];

[Anonymous]

Hi Andrey this worked perfectly. Thank you very much.

[andrey_krylov]

You're welcome)