Calendar object has to display all months disregard of data

[Anonymous]

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:

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

[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

[Anonymous]

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

[marcus_sommer]

Your script looked ok. and should work. Your min/max-generating could be optimized by loading the fieldvalues instead of the fact-table and you could use a preceeding load to create the calendar instead of creating a temp-table but this is just an optimizing and could be done later.

If you have more than one date-field (orderdate, shipping-date, billing-date ....) you might need to create more as one master-calendar and/or combine them within a canonical calendar - in this case take a look in my above mentined link which also cover these topics.

- Marcus

[#5]

canonical calendar sounds new to me, can you elaborate more please

Thanks

[marcus_sommer]

See here: Canonical Date.

- Marcus