Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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).
Hope this helps 🙂
HazelC
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')
)
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