Master Calendar with 2 tables

[#1]

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

[reddy-s]

Hi Renata,

Did you debug and and see the variables varMinDate and varMaxDate have the values you need? This could be a possible reason where its going wrong.

Beast Regards,

Sangram Reddy.

[#3]

I did and they have.

Thanks Sangram, but it is not it.