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

      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.