topic Get Year-Month range from start and end dates in QlikView

Get Year-Month range from start and end dates

shane_spencer — Mon, 26 Mar 2018 10:33:01 GMT

Hi folks,

I've got a QVD (attached) with a start and end date. I need to create a new field as per Obligation_Period below using these two fields, so a YYYY-MMM of the start date, and a YYYY-MMM of the month before the end date, and all the months between. All concatenated with a nice delimiter such as ; in the same field

i.e.

Any ideas? I cannot just do a start +1 month, start +2 month etc because the start and end ranges may vary in length.

Re: Get Year-Month range from start and end dates

sunny_talwar — Mon, 26 Mar 2018 11:46:24 GMT

Try this

Table:

LOAD start,

end,

due,

status,

received,

periodKey,

Concat(Obligation_Period, ';', Obligation_Period) as Obligation_Period

Group By start, end, due, status, received, periodKey;;

LOAD start,

end,

due,

status,

received,

periodKey,

Date(MonthStart(start, IterNo() - 1), 'YYYY-MMM') as Obligation_Period

FROM [..\..\Downloads\obligations.qvd] (qvd)

While MonthStart(start, IterNo() - 1) < MonthStart(end);

Re: Get Year-Month range from start and end dates

shane_spencer — Mon, 26 Mar 2018 12:03:19 GMT

Awesome! Thanks Sunny!