5 Replies Latest reply: Dec 12, 2016 4:32 AM by Marcus Sommer RSS

    Calendar object has to display all months disregard of data

    Rajalakshmi B

      Hi All,

       

      Here is a scenario, I need to display all the months in the calendar object even when there is no data for that particular month. The months are  displayed as shown below:

      calendar.PNG

      I need to display all the months from Jan to Dec even when there is no data. Please help me with a solution.

       

      Thanks in advance!!

       

      -Raji

        • Re: Calendar object has to display all months disregard of data
          Marcus Sommer

          I think it would be quite useful to generate all dates between your min- and max-date from your transaction-tables by using a master-calendar: How to use - Master-Calendar and Date-Values.

           

          - Marcus

            • Re: Calendar object has to display all months disregard of data
              Rajalakshmi B

              Hi Marcus,

              Thank you, I referred to the thread provided by you. But I am not clear as:

              Should the master calendar be created twice since the start date is different and end date is different.We need to get all the months for both start and end date.

               

              I am trying to use the below script provided by Josh (https://community.qlik.com/thread/48693)

               

              1.  QuartersMap:

              2.  MAPPING LOAD

              3.  rowno() as Month,

              4.  'Q' & Ceil (rowno()/3) as Quarter

              5.  AUTOGENERATE (12);

              6. 

              7.  Temp:

              8.  Load

              9.                min(OrderDate) as minDate,

              10.               max(OrderDate) as maxDate

              11. Resident Orders;

              12.

              13. Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

              14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

              15. DROP Table Temp;

              16.

              17. TempCalendar:

              18. LOAD

              19.               $(varMinDate) + Iterno()-1 As Num,

              20.               Date($(varMinDate) + IterNo() - 1) as TempDate

              21.               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

              22.

              23. MasterCalendar:

              24. Load

              25.               TempDate AS OrderDate,

              26.               week(TempDate) As Week,

              27.               Year(TempDate) As Year,

              28.               Month(TempDate) As Month,

              29.               Day(TempDate) As Day,

              30.               YeartoDate(TempDate)*-1 as CurYTDFlag,

              31.               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

              32.               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

              33.               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

              34.               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

              35.               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

              36.               WeekDay(TempDate) as WeekDay

              37. Resident TempCalendar

              38. Order By TempDate ASC;

              39. Drop Table TempCalendar;


              Thanks,

              Raji