All calculated dimensions are not created equally

Calculated dimensions can be a bear on performance in applications with large data volumes, but should they all be treated the same?

Ignore

The first category of calculated dimensions are the accidental ones, in which a developer somehow ended up with an equals sign before the field name in his dimension, but no true transformation. It turns out that these look like calculated dimensions, but aren't treated that way by Qlik. Rob Wunderlich recently tipped me off that a field reference preceded with an equals sign and nothing else is treated the same by the engine as if there is no equals sign.

Using the application/data model from my caching post, I tested copies of a table chart with and without an equals sign in the dimension and found that the cache was reused across the sheets. (I also confirmed this in QlikView November 2018.) The Qlik engine must treat the direct field references the same as those with an equals sign in the hypercube.

Thank goodness they're innocuous, because Qlik Sense makes it really easy to accidentally add equals signs, if you ever open the Expression Editor when modifying a dimension and click "Apply." Personally, I clean them up as a matter of professionalism, pride, and consistency.

Resolve

The next category is the second most common type of calculated dimension: deliberate, but unnecessary calculated dimensions. I see these most often:

1. to change the format of a field, ex. Date(FieldName, 'MMM DD')
2. concatenating field values together, ex. LastName & ' ' & FirstName
3. data cleansing, ex. If(Trim(FieldName) = 'A', 'B', FieldName)

In all these examples, the work should be pushed upstream to the script and data model, drastically improving the performance of the front end and honestly just making the application easier to use for developers and self-service users. These are easy to detect using the Qlik Sense Document Analyzer and subsequently resolve. The very modest trade off in the app's size if new fields are to be added should be more than offset by the improvement in responsiveness with each click on sheets with these calculated dimensions. Focus on these for quick improvement to application performance.

Live with it, if you must

There are a handful of functions returning arrays that are sometimes appropriate to use as calculated dimensions, such as Class and Aggr, but these should only be used when the expression results cannot be known at the time of script execution.

For instance, if you are using the Class function to bucket a field's values that do not change based on user selections, that could be done in the script and data model. However, if you are using Aggr to create an array of values that recompute with each click, that must be done in the front end.

There are also some functional reasons why you might want to have expressions in your dimensions.

• Because cycle groups from QlikView have not been ported over to Qlik Sense, one solution to recreate them is to dynamically populate a dimension based on a variable's value, ex. =[$(v_CycleDim)]. (A better performing but harder to maintain solution would be conditionally enabling/disabling dimension options, if the chart type supports it.)
• If you are browsing new data in the front end, the fields referenced in a chart can be dynamically populated in the same set of UI objects to save you the trouble creating a lot of new tables and filters to browse. Especially for those who miss quickly creating a Table Box with all fields in a table in QlikView, this can be handy.
• A quick example is creating a filter using the system field $Field and previewing the selected field's values using the calculated dimension $(=[$Field]).
• A more elaborate example is selecting a table from the $Table system field and using the filtered $Field values to populate an entire table showing the contents of the selected $Table value. This expression can be copied to each dimension in a table (better yet use a parameterized variable), updating the n to reflect the field number you wish to display in that column: $(='[' & SubField(Concat($Field, '|', $FieldNo), '|', n) & ']'). I keep this and the previous example in a sandbox application I reuse to explore data rather than creating an app from scratch each time.