Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Valued Contributor

Get Year-Month range from start and end dates

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.

obligationperiods.PNG

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

Tags (3)
1 Solution

Accepted Solutions
Highlighted

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

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

View solution in original post

2 Replies
Highlighted

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

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

View solution in original post

Highlighted
Valued Contributor

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

Awesome! Thanks Sunny!