How to make KPI Charts in Qlik Sense currency-friendly

When building KPI charts in Qlik Sense, automatic number formatting is a time-saving function that adjusts values using SI symbols to fit the available space. For example, it shows 875 as it is, 23,475 as 23.4k, and 45,600,000 as 45.6M.

But what happens when you need to display currency? Switching to the "Money" format removes this smart adjustment, forcing you to choose between potentially illegible KPIs due to long numbers, or fixed magnitude formatting (e.g., always in millions), which makes small values look ridiculous (0.000001 M).

Here is how I solved this limitation.

What is the issue with Qlik Sense currency-friendly without losing SI formatting

Symptoms:

• KPI charts lose dynamic SI scaling when using currency formatting
• Either the KPI becomes too long or small values look absurd

Example:

• £2,348,475 stays as £2,348,475 (no scaling)
• If forced to millions: £1 becomes £0.000001 M (fixed magnitude)

Why does this issue occur?

This is a limitation in Qlik Sense. The built-in "Money" format does not support dynamic SI scaling. It applies a fixed format, ignoring the value's magnitude.

How I overcame the Qlik Sense currency limitation

The workaround:

We debated using nested ifs to check the size of the measure and adjust formatting accordingly. This would work but impacted performance.

For technical reasons, which are far beyond the scope of this blog post, in the Qlik Engine, the calculation path of IF statements is counter-intuitive.

Step one: the engine calculates each possible outcome of the IF for every row of data in the hypercube, that is both the result if true and the result if false.

Step two: the IF statement is resolved and the result set is collapsed with the invalidated values discarded.

This means that the calculation cost of the IF statement grows rapidly with depth and the size of the data set. If we used this logic in multiple measures in the same sheet we could quickly find we over-run our resource limits.

I turned to mathematics for a solution and this was the result:

1. Use the log function to calculate the magnitude of the value (in thousands)
2. Apply a pick list to choose the appropriate format dynamically
3. To keep formulas readable, encapsulate the logic in a parameterised variable, allowing us to pass the relevant field for each KPI

This approach preserved dynamic scaling and added the currency symbol.

Detailed steps to fix Qlik Sense KPI currency formatting

Create a variable for dynamic formatting

Define a variable in Qlik Sense (e.g., CurrencyFormat) with the expression shown below (note: we do not want the value of this expression to be calculated, so we do not need a preceding ‘=‘):

You may notice that the expression looks a little odd. This is because we are creating a parameterised variable which acts like a custom function. When we call the variable, we provide $1 as a placeholder for the input we will use.

• '$1' is the parameter passed (e.g., your KPI measure)
• 'Log10' is used to calculate magnitude (I needed log1000 but Log n (X) = log (x) / log (n))
• 'Pick' selects the right format (Pick outputs the positional value from the list that matches the input, i.e., the number of thousands)

We use: =$(parameterizedVariable(Value to pass))

e.g., =$(CurrencyFormat(<MeasureValue>))

Apply the variable in KPI expression

To apply the variable in KPI expression we need data to work with. The following load script will create a table of random values of varying magnitudes: 

Once the data load is complete you can apply the formatting in your chart object as follows:

1. =$(CurrencyFormat(<MeasureValue>))

Ensure that you change the Number Formatting to “Measure Expression.”

Verification of the Qlik Sense currency formatting solution

By making selections validate that the KPI using the "CurrencyFormat" parameterized variable demonstrates the expected behaviour, i.e.:

Lessons learned from Qlik Sense parameterised variables

• With parameterised variables you can add complex functionality without impacting the readability of your expressions
• Parameterized variables improve reusability (no need to rewrite the formula for each measure that needs it)
• Nested ifs do not perform well in charts due to the cost of calculation

Conclusion

This workaround gives you the best of both worlds: currency formatting + dynamic SI scaling.

Have you used parametrized variables to improve your apps in Qlik Sense? What are your favourite features? Share your tips in the comments!

We’re the UK’s #1 Qlik Partner. With 250+ customers and 25 experts, we can help you with implementation, training, or support.

Not sure where to start? Book a call with our experts today!