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

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

    Antoine Vunc



      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;




        $(vMinDate) + IterNo() - 1         as Num,

        Date($(vMinDate) + IterNo() - 1)     as TempDate


        1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);




        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.