Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friends,
In the script below I am trying to get my master calendar to work using a MinMax table that uses FieldValue function rather than a resident load of dates.
But I cannot get the calender to work using this. Anyone got an idea of how to fix this?
/////---------------------------Script-------------------------------------------
Orders:
LOAD * INLINE [
OrderID, OrderDate
1, 20/11/2011
2, 20/11/2011
3, 01/02/2012
4, 31/01/2012
5, 01/06/2014
6, 15/07/2014
7, 01/01/2016
];
////----Master Calender--------
//MinMax1:
//LOAD
// Min(OrderDate) AS MinDate1,
// Max(OrderDate) AS MaxDate1
//RESIDENT Orders;
MinMax:
LOAD
Min(FieldValue('OrderDate',RecNo()))-1 as MinDate,
Max(FieldValue('OrderDate',RecNo())) as MaxDate
AutoGenerate(FieldValueCount('OrderDate'));
// ***** Set up the variables *****
LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMax'));
LET vMinDate2 = NUM(PEEK('MinDate', 0, 'MinMax'));
LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
// ***** Temporary Calendar *****
TempCal:
LOAD
DATE($(vMinDate) + ROWNO()-1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) +1;
//DROP TABLE MinMax;
// ***** The Master Calendar *****
MasterCalendar:
LOAD
TempDate AS OrderDate,
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,
CEIL(MONTH(TempDate)) AS Quarter1,
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,
MonthStart(TempDate) as MonthStart
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
Hi guys,
Thanks for your input.
I guess the last thing is I don't understand why it creates a "?" output? Never seen that before.
Is it a bug?
The "?" is an oddity when using an aggregation function -- like min() -- with FieldValue input. The internal value is correct, but it seems to be confused as to what to use for a display value. It doesn't cause any problem, especially since in normal circumstances you drop the fields.
-Rob