topic Please help explain this Master Calender code! in App Development

Please help explain this Master Calender code!

Mon, 23 Mar 2015 21:52:33 GMT

Hello there!

I recently came across a Master Calender script in Qlik Sense below. But I do not understand the code for WeekYear and IsLastMonth!

Can anyone please tell me what each line of code is doing? It looks complicated as I am not familiar with it

Any breakdwon would be fantastico!!

// ***** The Master Calendar *****

MasterCalendar:

LOAD

TempDate AS OrderDate,

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, //Splits the months into 3 categories.

Week(TempDate) & ' - ' & If(Week(TempDate)=1 and

Month(TempDate)=12,Year(TempDate)+1,

If(Match(Week(TempDate),52,53) and

Month(TempDate)=1,Year(TempDate)-1,

Year(TempDate))) as WeekYear,

If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)),1,0) as IsInYTD,

If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)),1,0) as IsInQTD,

If(Day(TempDate) <= Day($(vToday)),1,0) as IsInMTD,

If(Month(TempDate) <= Month($(vToday)),1,0) as IsCurrentMonth,

If(Month(AddMonths(TempDate,1)) <= Month($(vToday)),1,0) as IsLastMonth

RESIDENT CalTemp

ORDER BY TempDate ASC;

DROP TABLE CalTemp; // Tidy up the model by removing tables no longer required

Re: Please help explain this Master Calender code!

Wed, 25 Mar 2015 19:00:29 GMT

Hello anyone have an exaplnation for the code in colours above please?

Re: Please help explain this Master Calender code!

Ralf-Narfeldt — Thu, 26 Mar 2015 06:50:39 GMT

The blue code creates a field WeekYear with weeknumber and year, like '32 - 2014'. There is some code to handle what happens if it's a broken week (the week around New Year). From what I can see:

If it's week 1 and December, it sets it to next year.

If it's week 52 or 53 January, it sets it to previous year.

There is already a solution to handle broken weeks acc. to ISO 8601, and that's to use the weekyear function.

Week(TempDate) & ' - ' & WeekYear(TempDate) As WeekYear

http://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/weekyear.htm

Also the system variable BrokenWeeks comes into play

http://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/Scripting/NumberInterpretationVariables/BrokenWeeks.h…

The code in your example may have some special way of handling the broken weeks that is required. I haven't looked into how it differs from ISO 8601.

The red code sets IsLastMonth to 1 if the date is in the month before the current month.