6 Replies Latest reply: Aug 14, 2017 4:50 AM by Mohammed Al Radi RSS

    QuarterYear function in Master Calender

    Mohammed Al Radi

      Dear Qlik Community,

       

      I hope this finds you well. I would like to add a quarteryear function to my mastercalendar. As the data set that I work with deals with multiple years, it would be useful to be able to analyse via quarteryear i.e. Q12016,Q22017 etc...

       

      Would you by any chance know what expression would allow for that? I tried several combinations using the Quarter function in my mastercalender but no luck! Your help would be much appreciated.  Thank you!

       

      Please find enclosed the calendar that I am currently using for your reference:

       

      MinMax:

      LOAD Min([CP Date]) AS MinDate,

      Max([CP Date]) AS MaxDate

      RESIDENT [CP Page];

       

       

      LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

       

       

      MasterCalendar:

      LOAD

      Date($(vMinDate) + RecNo() - 1) AS [CP Date],

      Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

      Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

      Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear],

      ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter

       

       

      AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

       

      DROP TABLE MinMax;