Calculated Fields

[hic]

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 with the two methods? And how are the user interface fields calculated?

[hic]

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, I would say that 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. Just 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 that 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 today does it. In the example below, the table dimension is “=Aggr(Year(Date),Date)” and it also 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 on 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
Both Calculated dimensions and Fields-on-the-fly can cause performance problems, so it is a good idea to consider moving 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 (since Nov 2019) 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 - don't use a timestamp to create your calendar! Use a date instead:

Good luck!

HIC

Read also in Calculated Fields in Qlik Data Analytics .

[alex_nerush]

Hmm, the following sounds like undocumented features:

• ---

  @hic wrote:
  

  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

  ---

[Anonymous]

@hic : 2 Questions

- A quite old Scalability Center Performance Quick Tip 13 says that Charts with "Calculated Dimensions containing 2 fields" are excluded from Result Cache. For example the calculated dimension 

if (Country='Austria', left(Region,3), right(Country,2)) 

--> when I understand this article correctly, this is still true?

- When can we expect to have a "preload app" feature in Qlik Sense - similar to QlikView?  I know there are some Tools like EAPowerTools, but it seems to be the missing link to take real advantage of this new feature.

Thx,

Roland

[hic]

@alex_nerush : Yes, they are - at this stage - undocumented. But that will change...

@Anonymous : Yes, that is still true. Calculated dimensions that use more than one underlying field are tricky to handle.

[mountaindude]

@Anonymous 

I agree it would be nice to have preloading/cache warming built into Sense, out of the box.

Lacking that, the APIs do give us everything needed to solve this ourselves.  EAPowerTools does this (via Joe Bickley's Cache Initialiser), as does Butler-CW (which I created) and others.

I figured I'd give my view on this, as I've been working on this topic a fair bit over the years.

In principle I agree with you - this should be a feature built into the core product (Qlik Sense Enterprise on Windows in this case). That said, my own experience is that most organisations that need preloading/cache warming sooner or later need more sophisticated fine tuning of what's preloaded and what's not.
My suspicion is then that the more complex cache warming features that are demanded, the less likely are Qlik to include them in the core Qlik Sense product. It's just not part of their core product. 

I might very well be wrong about this - that'd be great actually as I could then sunset Butler-CW (and maybe other tools too). 

The upside of using dedicated tools for things like cache warming is - of course - that they give us flexibility to do what we need them to do, without being affected by Qlik's release cycles or product roadmaps. 

Pros and cons, as always.

[mmarchese]

@hic Thanks for this article!  It is very helpful.

I would love it if you could expand the discussion to cover how calculated fields are labeled in the user interface.  I believe you'd have to pick one of these 4 options for each scenario in the table below:

• The underlying field(s)
• The master dimension's name
• The master dimension's expression
• The master dimension's label expression

Scenario table:

	Column headers, chart legend and axis titles, etc.	The filter bar
Calculated dimension
Field-on-the-fly

Well, actually, you couldn't just pick one choice per scenario since labels are optional.  You'd have to specify a hierarchy for the cases in which labels were relevant, such as "dimension label expression > dimension name."  But labels often seem to be ignored anyway in my experience, at least for the filter bar.

Maybe it won't be so confusing now that I know about calculated dimensions and fields-on-the-fly, but to date, this issue has baffled me (and others).

[millerhm]

Thanks for elevating @mmarchese  - while I have moved more and more of these to the script as HIC mentioned - partly for performance and partly for this precise reason, it has long since made me crazy that these fields-on-the-fly do not use their label in the selection bar. 

[hic]

@mmarchese 

Your question is very relevant. But it only concerns Fields-on-the-fly in the selection bar. Calculated dimensions never appear in the selection bar and labels in other places are manually set - as they should be.

But for Fields-on-the-fly, I agree with you: There is a lot of room for improvement... but it is not as simple as one might think, and there are some considerations to be made:

1. The same expression may exist in several objects, and thus have different labels. Which label should you then use? If it is a Master item, then it's easy: Master items trump other labels.
2. The same object dimension can in fact create several selections. Just look at a histogram, where the dimension is a Class() function with variable bin width. Should the different selections use the same label? Then you could get many selections side by side that all have the same label.

But these problems can be solved, and I promise you to push for more development in this area. 

[mmarchese]

@hic Ah yes, the devil's in the details.  I'm glad it's on Qlik's radar though.  Thanks!

[Vegar]

Thanks @hic 

A really good  blog post. I learned something new. I've noticed the difference between the two calculated dimension behaviours before but haven't really given it any deep thoughts until now.

I've noticed that front end calculated dimensions is an frequent topic in the Community forums so I will for sure reference your post when needed in the future. 

BR Vegar

[skokenes]

@hic thank you for discussing Field-on-the-Fly, its something thats interested me since I saw it got added to the Engine API. I have a few follow up questions on how it works:

1. You mention that you suggested moving FotFs to master dimensions in Sense so they get created up front. Does that imply then that the performance hit of using them is a 1 time thing? Ie, they impact performance at the time they are created, but once indexed they work like other fields? Or do they cause slower performance in say, a hypercube calculation, as opposed to a persisted field?
2. Via the Engine API, we can name our FotFs. Let's say I create one with the Class() function and call it [MyCustomBin]:
   1. Are we able to use [MyCustomBin] like a native field in a list object, with search and selections?
   2. Are we able to use [MyCustomBin] in set analysis expressions?
   3. Will [MyCustomBin] be visible anywhere in the TablesAndKeys data that the Engine provides about a data model?
3. The EngineAPI has "AddFieldFromExpression" for adding a field on the fly. Is there a similar "Remove Field on the Fly" method? Would there be any benefit to dropping these FotFs if they become unused?