9 March 2023

Column references in Qlik Sense

Share this message
Referencing Columns in Qlik Sense Explained

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about column references in Qlik Sense.

Bitmetric Friday Qlik Test Prep 52

We were actually quite surprised when we encountered this answer and given the fact that the answers are almost spread equally between A and D it seems you are too!

The correct answer is A

Column references in Qlik Sense

The column() function in Qlik references a column in a straight table. Within the parentheses you can specify which column to target, Column(1) being the first column after all dimensions, since these are disregarded.

Qlik Sense Column Function

As we can see in the chart above referencing a column does not imply referencing the actual values in the column, but rather the expression used in it. In the reference column the expression is calculated again. That’s why the column gives different results when using the random function.

Calculation speed

The use of the Column() syntax is mostly one based on quick debugging, doing on the fly validation or summing multiple columns. And in the latter case, does it speed up things?

We have tested this on a dataset containing 5 million rows and a variable for margin:

SET vMargin = 1 - (Sum(Revenue) / Sum(CostPrice));

With the help of the Qlik Sense Document Analyzer Pro we can see the effect of the calculations in the duration of the load of the following tables:

We have ran the analyzer three times. One time with only the column containing the expression $(vMargin), The other time with $(vMargin) and Column(1). The final time with $(vMargin) in both colomns.

QSDA Pro is a great tool to analyze sheets and find out metric about the performance and where to improve. In this case the SaaS app [TST] Column (containing the tables pictured above) is loaded and evaluated:

Qlik Sense Document Analyzer Pro

Now if we press view, we can get an overview of all statistics of the app. We press the button ‘Sheets’ for the details on a sheet level:

QSDA Pro

It is quite clear that the loading times are quite long. It takes almost eight seconds to load the table with only the variable. But it is more interesting to see what happens with the other two. Using the Column() function to reference the first column is actually the slowest. It takes three to four seconds longer to load the extra column.

So be aware you want to calculate totals on a chart with many columns and measures. Using column will calculate all these measures again per column, accumulating to a heavy and slow loading chart.

That’s it for this week. See you next Friday?

Want more? Then subscribe to our newsletter, or click the button below for our full archive!

Expressions Friday Qlik Test Prep Solution

How can we help?

Feel free to contact us if you have any comments or questions.