3 Replies Latest reply: Feb 16, 2016 6:09 AM by Jérémy Bonde RSS

    Wrong month value (00)

    Jérémy Bonde

      Hello there,

       

      I would like to take the highest and lowest date from a table then make an other table witch contains each month between the min and the max date values.

       

      It looks like this :

      Tempotable:

      Load

          min(MonthOfYear) as tempMinDate, 

          max([Last Status Date]) as tempMaxDate

      resident Extract; 

       

      LET dateIterative = date(peek('tempMinDate') - day(peek('tempMinDate')) +1, 'dd-mm-yyyy');

      LET maxDate = date(peek('tempMaxDate')- day(peek('tempMaxDate')) +1, 'dd-mm-yyyy');

       

      Drop table Tempotable;

       

      LET currentMonth = month(dateIterative);

      LET currentYear = year(dateIterative);

       

      Calendar:

      load *

      inline [

      MonthOfYear, Month, Year

      $(dateIterative), $(currentMonth),$(currentYear)

      ];

       

      do while dateIterative <= maxDate

      dateIterative = AddMonths(dateIterative,1);

      currentMonth = month(dateIterative);

      currentYear = year(dateIterative);

      load *

      inline [

      MonthOfYear, Month, Year

      $(dateIterative), $(currentMonth),$(currentYear)

      ];

      LOOP;

       

      Everything work well except one thing : The month value I collect from the min and the max also is "00". Then in my table "Calendar", the min value is dec 2010 instead of jan. 2011 and the max value is dec. 2015 instead of jan. 2016. (the max value is 01-00-2016 and the min is 01-00-2011)

       

      The format of date from the resident table is like this : 05/01/2016  as dd/mm/yyyy and for the case of the max value I just import the value from an excel file without any treatment. I do some treatment for the min value but the problem is the same so I would like to know why I have this issue for the max value first.
      I tried to set the date without setting the day value to 1 :
      from :
      LET maxDate = date(peek('tempMaxDate') - day(peek('tempMaxDate')) +1, 'dd-mm-yyyy');
      to :

      LET maxDate= date(peek('tempMaxDate'), 'dd-mm-yyyy');


      But that wasnt the problem obviously.

       

      I hope I am clear. Thanks for your help !

      I have to say that I absolutly HATE how dates are managed in qlik sense