autocalendar

[Bart_Breekveldt]

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.

[YoussefBelloum]

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]

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

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)