3 Replies Latest reply: Feb 27, 2018 6:52 AM by Jose Cardoso RSS

    Create Master Calendar from date column - Sense App

    Jose Cardoso



      i have a table (generic_message_log) from where i want to take the column Date to create a Master Calendar of all dates in the column. Both tables are below. Question is that, when i'm adding fields as Master Items, like Year, Month,Week to create the Period (as per the Tutorials) the selection doesn't get updated. How can i get both tables linked on the Date column? (they are linked in the Data Model Viewer on Date field).





      [file_id] AS [m_file_id],

      Date([run_time],'DD-MMM-YYYY hh:mm:ss') AS [Date],

      [filename] AS [m_filename],

      [run_time] AS [m_run_time];

      SELECT "file_id",



      FROM "ftp_notifier"."generic_message_log";





      //Master Calendar

      LET varMinDate = NUM(PEEK('Date',0,'generic_message_log'));

      LET varMaxDate = NUM(PEEK('Date',-1,'generic_message_log'));




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

      AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;

      // Create the Master Calendar




      DATE(TempDate, 'DD-MMM-YYYY hh:mm:ss') AS Date,

      WEEK(TempDate) AS Week,

      YEAR(TempDate) AS Year,

      MONTH(TempDate) AS Month,

      DAY(TempDate) AS Day,


      DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

      'Q' & CEIL(MONTH(TempDate)/3)&'-'&YEAR(TempDate) AS QuarterYear,

      'W' & WEEK(TempDate)&'-'&YEAR(TempDate) AS WeekYear,

      WEEKDAY(TempDate) AS WeekDay


      RESIDENT TempCalendar

      Order BY TempDate ASC;

      DROP FIELD TempDate;