Calendar Table

[neerajthakur]

Temp:
Load
min("Invoice Date") as MinimumInvDate,
max("Invoice Date") as MaximumInvDate
Resident SalesDetails;
Load
min("CL_Key") as MinBudgetDate,
max("CL_Key") as MaxBudgetDates
Resident Budget;
Load
min("Actual Delivery Date") as MinDelDate,
max("Actual Delivery Date") as MaxDelDate
Resident SalesDetails;

LET varMinDate = Num(Peek('MinimumDate', 0,'Temp'));
LET varMaxDate = Num(Peek('MaximumDate', 0,'Temp'));
LET varToday = num(today());

Drop Table Temp;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + RowNo() - 1 AS Num,
Date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS DateKey,
TempDate AS "Invoice Date",
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I want to select MinimumDate and MaximumDate from line 3-12  there are three different types of dates for min and max , I want to include these dates so that calendar table gives date across all three dimensions instead of just one.

@PrashantSangle  I hope you can help 

[PrashantSangle · Accepted solution]

Hi, Error msg showing that $(varToday) is not populating correctly.

// LET varToday = num(today());

uncomment that variable and try.

if you want to debug your code then comment flag fields.

//inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
//inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

and then run.

Regards,

Prashant Sangle

[rubenmarin · Accepted solution]

Hi, this is another error, note that the error message has a comma betwen '>>>>>>' and '<<<<<<', telling you where it found the error, there is a comma alone, without the second parameter of the inyeartodate() function.

Follow the instructions give by @PrashantSangle to solve this one.