topic Re: Count Months Open from Date Submitted Qlikview in QlikView

Count Months Open from Date Submitted Qlikview

Anonymous — Fri, 26 Oct 2018 15:23:49 GMT

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.

Re: Count Months Open from Date Submitted Qlikview

Jesh19 — Fri, 26 Oct 2018 18:24:39 GMT

Check the below link.

MonthName between Start Date and End Date

Re: Count Months Open from Date Submitted Qlikview

andrey_krylov — Fri, 26 Oct 2018 19:43:25 GMT

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];

Re: Count Months Open from Date Submitted Qlikview

Anonymous — Tue, 30 Oct 2018 15:58:07 GMT

Hi Andrey this worked perfectly. Thank you very much.

Re: Count Months Open from Date Submitted Qlikview

andrey_krylov — Tue, 30 Oct 2018 17:13:20 GMT

You're welcome)