Master Calendar using Month-Year

[qlikwiz123]

I have Month Year column in MMM - YY format. How do I create Master Calendar using this column? I don't have Date level fields and only this Month Year. I am looking at the Master Calendar Script below but it requires a date to create Min and Max values and generate values in between.

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

               min(OrderDate) as minDate,

               max(OrderDate) as maxDate

Resident Orders;

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

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

DROP Table Temp;

TempCalendar:

LOAD

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

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

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

MasterCalendar:

Load

               TempDate AS OrderDate,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

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

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

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

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

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

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[Digvijay_Singh · Accepted solution]

Try this may be, I tested in text box and it gives me first day of the month, I hardcoded MMM-YY but it should work if you plug-in your field. 

Date(Date#('Dec - 23','MMM - YY'),'MM/DD/YYYY')

Other option could be to use MAKEDATE(Y,M,D) but for that you would need numeric mapping of your Month Name and Number and you would need to extract year part from your month year field.

[vikasmahajan]

Hi ,

You can create reporting date using MAKEDATE(Y,M,D)  as Digvijay suggested and use following link to create various flags ytd & rolling periods flags.

https://community.qlik.com/t5/Member-Articles/As-of-Table-with-YTD-MTD-QTD-amp-Calculation-of-Rollin...

Hope this help you.

Vikas

[qlikwiz123]

Hi @Digvijay_Singh 

I followed your idea and created dates. When I use this to create my Master Calendar, I found a small issue on how it is creating date buckets such as Quarters. When I select Q1 from 'Quarters', it is selecting first three months from current year as well as Previous year. 

Is there a way I can create separate Quarters for each year, which might say Q1 2022, Q2 2022..Q4 2023?

[Digvijay_Singh]

Do something like this - 

'Q' & CEIL(MONTH(Yourdatefield) / 3) & '-' & YEAR(Yourdatefield)

[qlikwiz123]

Yup! Figured it on my own. Appreciate your help 🙂