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:

 Gbillions1

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:

SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

 

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.

SET NumericalAbbreviation='3:K;6:M;9:B;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

On reload, the output changes to:

 gbillions2

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.)

 gbillions3

Here are the expressions from the screenshot:

$(fx_RoundThousands(v_Input, '$'))

$(fx_RoundThousands(v_Input))

The Contributors:

MikeSteedle

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. 

TAGS: Qlik, Qlik Sense

Related News

This white paper will discuss the areas that should be considered when planning your Qlik...

Read More
Read More