Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Please help explain this Master Calender code!

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 Replies
Not applicable

Re: Please help explain this Master Calender code!

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

Employee
Employee

Re: Please help explain this Master Calender code!

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.

Community Browser