Get Year-Month range from start and end dates

[shane_spencer]

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.

[sunny_talwar · Accepted solution]

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

[shane_spencer]

Awesome! Thanks Sunny!