Hi,
Why not use master calendar as below?
TEMP:
LOAD
num(min(OrderDate)) AS MinDate,
num(max(OrderDate)) AS MaxDate
RESIDENT
OrderDetails;
LET vMinDate = peek('MinDate', 0, 'TEMP');
LET vMaxDate = peek('MaxDate', 0, 'TEMP');
DROP TABLE TEMP;
DateIsland:
LOAD
date($(vMinDate) + rowno() - 1) AS D,
year($(vMinDate) + rowno() - 1) AS Y,
month($(vMinDate) + rowno() - 1) AS M,
date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE
vMaxDate - vMinDate + 1;
TEMP:
LOAD
num(min(OrderDate)) AS MinDate,
num(max(OrderDate)) AS MaxDate
RESIDENT
OrderDetails;
LET vMinDate = peek('MinDate', 0, 'TEMP');
LET vMaxDate = peek('MaxDate', 0, 'TEMP');
DROP TABLE TEMP;
DateIsland:
LOAD
date($(vMinDate) + rowno() - 1) AS D,
year($(vMinDate) + rowno() - 1) AS Y,
month($(vMinDate) + rowno() - 1) AS M,
date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE
vMaxDate - vMinDate + 1;
And then you could create some flags:
| inyeartodate(D, $(vToday), 0) * -1 AS CYTD, | |
| inmonthtodate(D, $(vToday), 0) * -1 AS CMTD, | | |
And then you could create some flags:
| inyeartodate(D, $(vToday), 0) * -1 AS CYTD, | |
| inmonthtodate(D, $(vToday), 0) * -1 AS CMTD, | | |
