Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bart_Breekveldt
Contributor III
Contributor III

autocalendar

Dear community,

Our company delivers reports every 4 weeks (which results in 13 instead of 12 periods per quarter). However the AutoCalendar (which you can link to a dimension) is only set in months. How can I add script so I can filter on period in the Analysis tab?

Thanks in advance.

Labels (1)
3 Replies
YoussefBelloum
Champion
Champion

Hi,

on the reports you get, what Date fields you have ? Month ? Week ? Date ?

depending on your date fields, different solution may be considered

Bart_Breekveldt
Contributor III
Contributor III
Author

[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],

DERIVE FIELDS FROM FIELDS [DATUM-TIJD] USING [autoCalendar] ;

This is the complete script as it stands now. It contains the datefunctions: Year, Quarter, YearQuarter, _YearQuarter, Month, YearMonth, _YearMonth, Week, Date, _Date, InYTD, YearsAgo, InQTD, QuartersAgo, QuarterRelNo, InMTD, MonthsAgo, MonthRelNo, InWTD, WeeksAgo, WeekRelNo.

Bart_Breekveldt
Contributor III
Contributor III
Author

Summarized I want to have te oppertunity to select Period, YearPeriod and YearWeek as dimension as in the dropdownlist below. (''leverdata" means deliverydates, "dimensie toevoegen" is add dimension, "velden" is fields in English)

Qlik AutoCalendar.PNG