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

    Master Calendar with 2 tables

    Renata Campos

      Hi,

       

      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());

       

      TempCalendar:

      LOAD

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

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

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

       

      LOAD

      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?

       

      Renata