6 Replies Latest reply: Dec 1, 2016 2:28 PM by Jes Lee RSS

    Date function

    Jes Lee

      I loaded data from an excel file, and the date  is not displaying correctly in the visualization.  I am using the master calendar as well.   

       

      There are gaps between the dates --visualization is attached along with the QVF.

       

       

      The original date is in the following format :  MM/D/YYYY

       

      Master Calendar:

      QuartersMap:

      MAPPING LOAD

      rowno() AS Month,

      'Q' & Ceil (rowno()/3) AS Quarter

      AUTOGENERATE (12);

       

       

      Temp:

      LOAD

      min(DATE) AS minDate,

      max(DATE) AS maxDate

      Resident Coincident_Index;

       

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;

       

       

      TempCalendar:

      LOAD

      $(varMinDate) + Iterno()-1 As Num,

      DATE($(varMinDate) + IterNo()-1) AS TempDate

      AutoGenerate 1 While ($(varMinDate) + IterNo() -1) <= $(varMaxDate);

       

       

      MasterCalendar:

      LOAD

          TempDate AS DATE,

          Week(TempDate) AS Week,

          Year(TempDate) AS Year,

          Month(TempDate) AS Month,

          Year(TempDate)*100+Month(TempDate) AS MonthYear,

          Day(TempDate) AS Day,

          ApplyMap('QuartersMap', month(TempDate), Null()) AS Quarter,

          Year(TempDate)&ApplyMap('QuartersMap', month(TempDate), Null()) AS QuarterYear,

          Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

          WeekDay(TempDate) AS WeekDay

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;