2 Replies Latest reply: Jan 5, 2016 12:09 PM by Renata Campos RSS

    Master Calendar with 2 tables

    Renata Campos



      I am trying to create a dynamic table to make it easy to visualize sales engineers actual booking performance and compare it to their monthly targets.

      The problem that I have is that the info for booking values and sales targets comes from 2 different tables. On both date format is DDMMYYYY.


      Booking values comes from a table called SALESTABLE and field name is CREATEDDATETIME

      Monthly targets comes from a table called TARGETS and field name is PLANMONTH


      The problem that I have is when using dimensions YEAR AND MONTH, it creates 2 sets of dimensions YEAR(PLANMONTH) AND YEAR(CREATEDDATETIME), when I try to put both on a table it does not work.


      I thought the solution for that was to create a Master Calendar, with I did, but id does not work.

      The script that I am using for the master calendar is:


      /*Master Callender prepared based on all dates avaliable*/


      LET varMinDate = Num(rangemin(Peek(‘CREATEDDATETIME’, 0,'SALESTABLE'),Peek(‘PLANMONTH, 0,' TARGETS')));

      LET varMaxDate = Num(rangemax(Peek(‘CREATEDDATETIME’, 0,'SALESTABLE'),Peek(‘PLANMONTH, 0,' TARGETS')));

      LET vToday = num(today());




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

      Date($(varMinDate)+Iterno()-1) AS TempDate

      AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());



      TempDate as DAILY_Date,

      week(TempDate) as Week,

      Year(TempDate) as Year,

      Month(TempDate) as Month,

      Hour (TempDate) as Hour,

      Day(TempDate) as Day,

      Year2date(TempDate)*-1 as CurYTDFlag,

      Year2date(TempDate,-1)*-1 as LastYTDFlag,

      Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

      Week(TempDate)& '-'&Year(TempDate) as WeekYear,

      date(Today()-1) as Yesterday,

      Week (today()) as CurrentWeek,

      (Week (today()))-1 as LastWeek,

      Weekday(TempDate) as WeekDay


      resident TempCalendar

      order by TempDate Asc;


      Drop Table TempCalendar;


      But it does not work. Any clues about what I am doing wrong?