2 Replies Latest reply: Jul 17, 2018 11:58 AM by Ancuta Barbu RSS

    how to display last 3 months starting from a MonthYear date

    Ancuta Barbu

      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