Column references in Qlik Sense

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.

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.

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:

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:

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?

More from the Bitmetric team

Qlik Cloud Backup

Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.