Hello,
I would like to create a masterdata calendar and use date for create set analysis with last mont, last year etc.
I have a file with Date with a format YYYYMM
I created a Date DD/MM/YYYY like this 01&'/'&right([Date],2)&'/'&left([Date],4) AS Date,
After that I created a script to create a Data Table :
Temp: //Using the fact to extract min and max date
LOAD Min(Date) as minDate,
Max(Date) as maxDate
Resident FactTable;
LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(vMinDate) + IterNo() - 1 as Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate
1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
MasterCalendar:
LOAD
TempDate as 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,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,
MonthName(TempDate) as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar;
Questions :
- Is it the better solution to create a Master Data calendar ?
- Now how can I created value for set analysis:
- last month
- last year
- last 12 month
- How use this value in set analysis
Many thanks for your answer, it is a pleasure to read lot of article here.
