Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

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.

1 Solution

Accepted Solutions
sunny_talwar

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
sunny_talwar

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
Specialist
Specialist
Author

Awesome! Thanks Sunny!