gbillions and gbillions: changing default numeric abbreviations in Qlik Sense
A minor but frequent pain point at our Qlik Sense clients has been addressed in the Qlik Sense April 2018 release: you can customize the numeric abbreviations.
You may have seen something like this in a KPI object before:
In the US, the "G" abbreviation for billions of is uncommon and can be confusing, to users, while “B” is more commonplace. You know, "G" as in "gbillions"? (The "G" is for "giga-" in the metric system.) Now this can be customized.
Beginning with the April 2018 release, when you create a new Qlik Sense document and view the script, the system variable for the numeric abbreviations is included, by default:
The format looks a little funny, but it is a list of value pairs of exponential scale:abbreviation,
e.g. 3:k = 10^3, or 1000, or 1k
For legacy Qlik Sense applications, to change the default, you will have to add the above line to the load script. (Better yet: add this to a centralized include file, to be inherited by all applications.) The version below has been updated to capitalize K and switch billions to B.
On reload, the output changes to:
There is a way to accomplish the same automated rounding by thousands, in all object types, and even in QlikView, but it is more resource-intensive. The example below only includes thousands/millions/billions, and I implemented it using a parameterized variable, so the insane logic can be reused without rewriting it.
SET fx_RoundThousands = Num($($1) / RangeMin(Pow(10, ((Ceil(Len(Round($($1), 1))/3, 1) - 1) * 3)), 1000000000), $2 & '#,##0.00' & Pick(RangeMin(Ceil(Len(Round($($1), 1))/3, 1), 4), '', 'K', 'M', 'B'));
It looks very complicated, but is essentially just rounding to the nearest whole number, then using the length of that number to determine how much to divide the original number by and which abbreviation to suffix the result with. There is also an optional second parameter, if you would like to include a currency symbol. (It's largely complicated to try to avoid a nested IF.)
Here are the expressions from the screenshot:
Mike Steedle is a Chief Solutions Architect with Axis Group and has over 10 years experience as a business intelligence and Data Warehousing consultant. He attended the University of Notre Dame and has a degree in Management Information Systems. During his free time, Mike enjoys spending time with his dogs.