Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I fetch data from excel The latest date is 10/02/2023.
use code
Result
But I use this formula for my calendar.
But the dates 09/02/2023 and 10/02/2023 disappeared
I need E_Date and E_Day data for Listbox.
But the dates 09/02/2023 and 10/02/2023 disappeared
Hi@Sxbbb
You may want to take the min and max dates and build an entry for each date to generate all possible dates.
Something like this.
//******** Extract maximum and minimum date *************************
Temp:
LOAD Max(Date) AS MaxDate,
Min(Date) AS MinDate
RESIDENT [GRAND FACT];
//******** Store Minimum and maximum dates as variable ******************
LET varMaxDate = Peek('MaxDate', 0, 'Temp');
LET varMinDate = Peek('MinDate', 0, 'Temp');
//LET varMaxYear = Peek('MaxYear', 0, 'Temp');
//LET varMaxMonth = Peek('MaxMonth', 0, 'Temp');
DROP TABLE Temp;
//******************* Generate all possible dates ***********************
TEMP_CALENDER:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<=
$(varMaxDate);
//Building the master calendar with most date
//**************** Generate Calender ********************
CALENDER:
LOAD *,
Quarter&'-'&Year AS QuarterYear
;
LOAD
DATE(TempDate,'dd/MM/yyyy') AS Date,
Year(TempDate) AS Year,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Day(TempDate)AS Day,
Week(TempDate) AS Week,
Month(TempDate) AS Month
RESIDENT TEMP_CALENDER
ORDER BY TempDate Asc;
Drop table TEMP_CALENDER;
EXIT SCRIPT;