This content has been marked as final. Show 2 replies
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
order by TempDate Asc;
Drop Table TempCalendar;
But it does not work. Any clues about what I am doing wrong?