The Declare statement creates a definition for a field or group of fields. For example, Qlik Sense will automatically create extra fields whenever it finds a field containing dates. It automatically creates an autoCalendar using the Declare function.
Let's take a look at the code:
[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] ;
Qlik Sense calls this group of fields autoCalendar. This autoCalendar contains a group of different fields with date-related data. The variable $1 will hold the value of a field and it then applies the different expressions.
In this example, the autoCalendar declaration has 21 fields. This is the same as applying all these formulas to your date field yourself. However, we don't always know how to do it nor remember all of the expressions, so Qlik Sense comes to the rescue and does it for us. Any fields derived from a declared statement will not show in the Data Model Viewer. Instead, they will show in the Fields section in the Sheet Editor.
The Declare syntax is as follows:
definition_name:
Declare [Field[s]] Definition [Tagged tag_list ] [Parameters parameter_list ] Fields field_list
The last part of the auto-generated script derives the source date fields from the previously declared structure. The Derive syntax can be one of the following:
Derive [Field[s]] From [Field[s]] field_list Using definition
Derive [Field[s]] From Explicit [Tag[s]] tag_list Using definition
Derive [Field[s]] From Implicit [Tag[s]] Using definition
In our example, sense derives two date fields using autoCalendar:
DERIVE FIELDS FROM FIELDS [MONTH_BUS_KEY], [Full Month] USING [autoCalendar] ;
We will have a total of 42 new fields in the Sheet Edit that we can use in our charts.
The Calendar is the most common example you will find online to explain the Declare and Derive syntax. When you can leverage the power of the derived field is when you start mastering Qlik Sense. After all, that's the name of the book, isn't it?
Allow us to show you how the Declare and Derive functions work with an example.
We usually work with numbers which represent money. We also format these numbers in different ways within our charts. For example, we might want to show $1m instead of 1,000,000. These formats come out of the box in Sense; however, for billions, Sense uses G, which is not meaningful to many business users. Besides that, we might want to separate our amounts into buckets to show, for example, that 1,000,000 falls within the >500k bucket.
The following code will help us explain the example I just described.
Create a new application and open the Data Load editor from the navigation menu. Then copy the following code into the main section:
/*I load sample data using Inline load */
myDataTable:
Load * Inline
[
FullName, ProductType, Amount
Maria Johns, Personal Loan Type A, 10000
Peter Smith, Car Loan, 25000
George Thomson, Car Loan, 16000
Paula Mays, Personal Loan Type B, 55000
Patricia Gonzalez, Mortgage 5 yr fixed, 750000
];
/*I categorize my field Amount using tags */
TAG FIELD Amount WITH '$myAmount';
/* Here we declare a definition for fields with the tagged $myAmount */
[DeclaredValues]:
DECLARE FIELD DEFINITION Tagged ('$myAmount')
FIELDS
Dual('USD '&round($1/1000,0.1)&'K', $1) AS AmountInThousands Tagged ('$thousands', '$myAmount')
,Dual('USD '&round($1/1000000,0.1)&'M', $1) AS AmountInMillions Tagged ('$millions', '$myAmount')
,if(($1)>=0 and ($1)<20000, '0-20K'
, If(($1)>=20000 AND ($1)<50000, '20-50K'
, '+50K')) AS AmountGroup Tagged ('$amoungroup', '$myAmount')
;
/*Here we derive the files using DeclaredValues */
DERIVE FIELDS FROM EXPLICIT TAGS '$myAmount' USING DeclaredValues;
In this example, we first load the sample data into an inline table. In the second part of the code, we declare the fields in a table called DeclaredValues. We declare three fields: AmountInThousands, AmountInMillions, and AmountGroup. These fields can then be used in the charts as dimensions.