Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello anyone have an exaplnation for the code in colours above please?
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
Also the system variable BrokenWeeks comes into play
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.