Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
jgreen95
New Contributor III

Add Week ending date function to autocalendar

I know there is the standard Weekend() function that picks the last second of the Sunday of that week, does anyone know how to add this to the auto calendar scripting?

every time I try it just gives me an error and cant find anything online about it.

Labels (2)
3 Replies
Mauritz_SA
Contributor III

Re: Add Week ending date function to autocalendar

Hi there

If you just want to change/update the script itself the following should work :

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo],
Weekend($1,0,0) AS [Weekend]
;

I don't know how you would update Qlik Sense so that it is included by default.

Good luck.

Mauritz

jgreen95
New Contributor III

Re: Add Week ending date function to autocalendar

That's what I initially tried, it doesn't like if you change the standard autocalendar script. I ended up just copying the script out and making my own calendar with the values week/end, month/end ect.. was the easiest way to do it. You then end up with 2 autoscripted sections for some reason but it works

Mauritz_SA
Contributor III

Re: Add Week ending date function to autocalendar

I hardly ever use the data manager so I just unlocked the Auto-generated section and updated the script. We control the data model from outside the app so I wrote a function that looks for fields flagged as dates and then it builds the auto calendar for those fields. This allows me to add things such as days ago in the auto calendar as well.