Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Customize Number formatting in Qlik Sense?

Does anyone know how to customize number formatting in Qlik Sense, such that when you're formatting a measure total like $16,309,452,155.86, it can read instead like $16.31B, where both the "$" and "B" is appended to the decimaled number? This used to be a nice feature in Qlikview where the "K, M, or B" symbols could be added to the chart values, but having trouble finding the equivalent in Qlik Sense. This is the number format I would like to use, for instance, on the gauge chart visualization.

12 Replies
HazelC
Contributor
Contributor

Hi,

Been struggling with this myself but realised Qlik was almost doing this for me. The default Auto pattern is 0A. I changed this to £0.0A and viola! I get values in gbp to 1 decimal place but to and equivalent power (ie billions, millions, thousands).

HazelC_0-1593539013709.png

 

Hope this helps 🙂

HazelC

DvGogh
Partner - Contributor
Partner - Contributor

Jagan has a valid solution, though not optimal. Nested IF statements are sadly not calculated in sequence to preventing calculations of false preconditions. Qlik actually calculates all possible outcomes which can cause performance issues.

Rather than to evaluate the length with an IF statement, I would suggest using the length of the field as input for a pick-list. Even though this requires more code, the calculation only happens twice (once to determine the length, once for the desired presentation format)

-edit-

Based on Tealowk good point, with a little adjustment to use ceil instead of floor, plus a floor to fix decimal point results, we get a lot smaller list:

pick(ceil(len(floor(sum(MeasureName)))/3),
Num(Sum(MeasureName), '$####.00'),
Num(Sum(MeasureName)/1000, '$####.00K'),
Num(Sum(MeasureName)/1000000, '$####.00M'),
Num(Sum(MeasureName)/1000000000, '$####.00B'),
Num(Sum(MeasureName)/1000000000000, '$####.00T')
)

tealowk
Partner - Contributor III
Partner - Contributor III

That's a good workaround. To avoid repeating the same formats three times, I suggest the following formula inside the Pick() function:

Floor(len(sum(MeasureName))/3)+1