Calculated Fields in Qlik Data Analytics

Alan_Slaughter — Thu, 27 Jul 2023 09:41:35 GMT

This article provides insight into how to use calculated fields in Qlik Data Analytics (, , and ).

Calculated fields are often created in the script and stored under new aliases. But you can also create them in the user interface. What are the pros and cons of the two methods? And how are the user interface fields calculated?

Normally, new fields are created in the script and stored as additional columns in the data model. Just write your expression inside a Load statement, and you’re done:

But you can also do the same thing in the user interface, and then it could look like this:

So, which way should you do it?

Generally, you should put as much as possible in the script. In most cases, it is far better to have these calculations pre-made so that they do not have to be calculated at run-time, i.e. when the user clicks.

The Qlik Engine has two fundamentally different ways to calculate such expressions: As “Calculated dimension” or as “Field-on-the-fly”. The engine automatically decides how a specific calculation should be made, depending on the expression.

Fields-on-the-fly

This method was introduced in one of the early versions of Qlik Sense. As the expression is evaluated, the engine creates an additional column in the data model with a corresponding symbol table. As for a real field, the selection is stored in state vectors linked to this column.

In the picture below you can see a table dimension defined as =Year(Date), which results in four rows.

Now look at the selection bar: When a selection is made, the corresponding year is selected in the Field-on-the-fly called =Year(Date), a field which does not exist in the original data model. And in the selection bar, you can see that the selection is indeed stored in this virtual field, and not in the Date field.

Calculated dimensions

This is the old-fashioned way, and this is how QlikView still does it today. In the example below, the table dimension is =Aggr(Year(Date),Date) and results in four rows. Logically, this expression is equivalent to the above one.

But here the selection is instead made in in the underlying field: in the Date field.

It is always possible to create a Calculated dimension, no matter what the expression looks like. But the same is not true for Fields-on-the-fly. There are limitations to when they can be generated:

The expression must be based on one single field only, or multiple non-key fields from the same table in the data model
The expression cannot depend on the selection state, e.g. through the GetSelectedCount() function
The expression cannot contain an Aggr() function

If a Field-on-the-fly cannot be generated, the expression will be evaluated as a Calculated dimension instead.

Performance

Calculated dimensions and Fields-on-the-fly can cause performance problems, so it is a good idea to move them to the script instead. Fields-on-the-fly can almost always be moved to the script.

For Fields-on-the-fly, the performance problems become especially severe if the underlying field has many distinct values. A common example is when calendar functions like Year and Month are used on a timestamp with millions of distinct values, rather than on a date with fewer values, like 2 x 365 dates. Further, since Fields-on-the-fly are added to the data model, and the hash of the data model is used in the ID of the cache entry, Fields-on-the-fly can prevent the cache from being re-used properly.

To improve the performance, Master dimensions containing Fields-on-the-fly are now calculated already when the first user opens the app, something which can increase the time it takes to open a document. On the other hand, this will improve the response time considerably in the analysis phase, as well as mitigate cache problems, so we are confident that this is a correct decision. Hence, put your Fields-on-the-fly in the Master dimensions!

Should you want to tweak the behavior of the engine, you can always try the following:

Using UseAutoFieldOnTheFly=0 in Settings.ini will disable Fields-on-the-fly for all documents served by the engine
Using Set QlikInternalDisableFotfMode=1; in the script will disable Fields-on-fly in the app
Using Set QlikInternalDisableFotfPregen =1; in the script will prevent Fields-on-fly from being pre-calculated when the app is opened
Wrapping the expression in =CalcDim(…) will force it to be a Calculated dimension

But most importantly: Do not use a timestamp to create your calendar! Use a date instead:

Date(Floor( DateAndTime )) as Date,

article Calculated Fields in Qlik Data Analytics in Official Support Articles

Calculated Fields in Qlik Data Analytics

Fields-on-the-fly

Calculated dimensions

Performance

Related Content