How to create on your own an Autocalendar (Derived fields)

[Sonja_Bauernfeind]

The information and script in this article are provided as is. For further customization and assistance, engage with our active QlikView or Qlik Sense app-building boards.

If you have a group of fields that are related, or if fields carry information that can be broken up into smaller parts that are relevant when creating dimensions or measures, you can create field definitions that can be used to generate derived fields. One example is a date field, from which you can derive several attributes, such as year, month, week number, or day name. All these attributes can be calculated in a dimension expression using Qlik Sense date functions, but an alternative is to create a calendar definition that is common for all fields of date type. Field definitions are stored in the data load script.

Note in case that you need to change the format / to customize the date you need to do it from the script, and most probably drill down to all levels, depending of course the object and what you want to accomplish.

Here is such an example of an autocalendar that can be used in any case:

[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 [Date] USING [autoCalendar] ;

[RochaPedro]

Hello , how I can make half year derived in autocalendar ? 

I make this: 

Dual('H'&Num(Ceil(Num(Month($1))/6)),Num(Ceil(NUM(Month($1))/6),00)) AS [Half] Tagged ('$half', '$cyclic'),

or

'HY' & ceil(month(date#(($1),'MMM YYYY'))/6) as HalfYear,

The derivation was created, but in the graph it does not look like the year next to it is only half. 

My Script: 

[Calendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Ano] Tagged ('$axis', '$year'),
//Dual('H'&Num(Ceil(Num(Month($1))/6)),Num(Ceil(NUM(Month($1))/6),00)) AS [Half] Tagged ('$half', '$cyclic'),
//'HY' & ceil(month(date#(($1),'MMM YYYY'))/6) as HalfYear,
ceil(Month($1)/6)&'º Sem' as [Semestre],
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Trimestre] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [Ano Trimestre] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
//Dual('H'&Num(Ceil(Num(Month($1))/6)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Mês] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [Ano/Mês] 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 [Semana] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [DATA] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
weekday($1) as [Dia da Semana],
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [C5_EMISSAO] USING [Calendar] ;

Its ok!

In half year: 

it does not look like the year next to it is only half. 

Help

[Sonja_Bauernfeind]

Hello @RochaPedro !

I'd recommend taking this over to the respective board for App building (Qlikview or Qlik Sense, depending on what you are working with). You'll be able to get the right eyes on your query there!

I grabbed you the links to the boards:  QlikView or Qlik Sense 

[luciano_pw]

[autoCalendar]:
DECLARE FIELD DEFINITION TAGGED ('$timestamp')
FIELDS
DAYNAME($1) AS [date],
YEAR($1) AS [year],
MONTH($1) AS [month],
MONTHNAME($1) AS [month_name],
CEIL(MONTH($1) / 3) AS [quarter],
CEIL(MONTH($1) / 6) AS [semester],
DUAL(YEAR($1) & '/' & CEIL(MONTH($1) / 3), MONTHSNAME(3, $1)) AS [quarter_name],
DUAL(YEAR($1) & '/' & CEIL(MONTH($1) / 6), MONTHSNAME(6, $1)) AS [semester_name],
WEEKDAY($1) AS [weekday],
DAY($1) AS [day],
HOUR($1) AS [hour],
MINUTE($1) AS [minute],
TIME($1) AS [time],
WEEK($1) AS [week_of_year],
WEEKNAME($1) AS [week_year],
FLOOR((WEEKNAME($1) - WEEKNAME(MONTHSTART($1), -1)) / 7) AS [week_of_month],
DUAL(
MONTH($1) & '/' & FLOOR((WEEKNAME($1) - WEEKNAME(MONTHSTART($1), -1)) / 7),
NUM(NUM(MONTH($1)) & FLOOR((WEEKNAME($1) - WEEKNAME(MONTHSTART($1), -1)) / 7))
) AS [week_month],
DUAL(
MONTHNAME($1) & '/' & FLOOR((WEEKNAME($1) - WEEKNAME(MONTHSTART($1), -1)) / 7),
IF(MONTHNAME(WEEKSTART($1)) = MONTHNAME($1), WEEKSTART($1), MONTHSTART($1))
) AS [week_month_name]
;

[GerryCast]

Why doesn't this calendar table show up in the data model viewer in QS ?