0 Replies Latest reply: Jun 9, 2018 8:27 AM by Antoine Vunc

# Master Data Calendar and set analysis with last month, last year

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.