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: 
Anonymous
Not applicable

how to display last 3 months starting from a MonthYear date

Hi,

The end result should look like this:

ProductionDateFour_Months_rolling
2016012015 Oct";"2015 Nov";"2015 Dec";"2016 Jan
2016022015 Nov";"2015 Dec";"2016 Jan";"2016 Feb
2016032015 Dec";"2016 Jan";"2016 Feb";"2016 Mar
etc

I need the code to be in the Data Load Editor so I wrote something like this:

Load *,

Date(Date#(ProductionDate - 3, 'YYYYMM'), 'YYYY MMM') & '";"' &  Date(Date#(ProductionDate - 2, 'YYYYMM'), 'YYYY MMM') & '";" ' & Date(Date#(%ProductionDate - 1, 'YYYYMM'), 'YYYY MMM') & '";" ' & Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;

Load * inline

[ProductionDate

    201601

    201602

    201603

    201604

    201605

    201606

    201607];


  It works partially but for the first row for example, 201601, I see something like ";";";" 2016 Jan. I know is because of the month and -3, -2 etc. but I don't know how I should change the year at the same time, to make it run always correct.

Any ideas how could this work?

Thank you very much!

Anca

1 Solution

Accepted Solutions
sunny_talwar

May be try this

LOAD *,

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -3), 'YYYY MMM') & '";"' &

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -2), 'YYYY MMM') & '";"' &

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -1), 'YYYY MMM') & '";"' &

Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;

LOAD * INLINE [

    ProductionDate

    201601

    201602

    201603

    201604

    201605

    201606

    201607

];

View solution in original post

2 Replies
sunny_talwar

May be try this

LOAD *,

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -3), 'YYYY MMM') & '";"' &

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -2), 'YYYY MMM') & '";"' &

Date(AddMonths(Date#(ProductionDate, 'YYYYMM'), -1), 'YYYY MMM') & '";"' &

Date(Date#(ProductionDate, 'YYYYMM'), 'YYYY MMM') as Four_Months_rolling;

LOAD * INLINE [

    ProductionDate

    201601

    201602

    201603

    201604

    201605

    201606

    201607

];

Anonymous
Not applicable
Author

Thank you very much! I've found the same solution 10 min ago and it works indeed.