Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I'm having trouble with the master calendar.
the field date I'm using is "dat", when I create a list box with the expression "year(dat)" as a field and filter with it, the values are good.
But when I use the year I've created in my master calendar, the values are completely wrong:
here is my master calendar:
//******************* MinMax table**************
//
MinMax:
LOAD
Min(dat) as MinDate,
Max(dt) as MaxDate
Resident test;
//
//
//
//******* defining Min and Max dates**********
//
LET vMinDate= Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate= Num(Peek('MaxDate',0,'MinMax'));
LET vToday=$(vMaxDate);
//
//************ creating the temp calendar******
//
TempCal:
LOAD
Date($(vMinDate)+ RowNo()-1) as TempDate
AutoGenerate
$(vMaxDate)-$(vMinDate)+1;
DROP Table MinMax;
//
//************ the MasterCalendar table ******
MasterCalendar:
LOAD
TempDate as dat,
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, $(vToday),0)* -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday),-1)* -1 as LastYTDFlag
Resident TempCal
Order By TempDate ASC;
DROP Table TempCal;
--------------------------
the dat field in my test table is well formated. How come I'm having such a trouble?
Thanks
Not sure if this is related, but Min(dat) and max(dt)? Is it dt or dat?
hope this is a typing mistake
MinMax:
LOAD
Min(dat) as MinDate,
Max(dt) as MaxDate // it should be Max(dat) as MaxDate
Resident test;
Also use
TempCal:
LOAD
Date($(vMinDate)+ IterNo()-1) as TempDate
AutoGenerate 1
While $(vMinDate)+ IterNo()-1 <= $(vMaxDate);
Not it's well written as dat.
I will try to change this:
TempCal:
LOAD
Date($(vMinDate)+ IterNo()-1) as TempDate
AutoGenerate 1
While $(vMinDate)+ IterNo()-1 <= $(vMaxDate);