Please help explain this Master Calender code!

[#1]

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

[#2]

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

[Ralf-Narfeldt]

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/we...

Also the system variable BrokenWeeks comes into play

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

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.