Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Qlik Sense is shifting gears with the automatic nice number interpretation. Although not perfect in Sense version 1.1 (not customizable for different languages and to money values), it is very convenient representation that - once getting used to, you don't want to miss in QlikView KPI representations any more.
I am talking about the Sense KPI Object (since Sense 1.1) and the axis values in charts.
I posted into IdeaGlow the wish to get a simple new ScriptFormula "NiceNum(.....)" to follow exactly the same representation rules as Qlik Sense uses.
Examples:
I created a copy of NiceNumber called NiceNumber2 which expects 2 arguments. In fact it injects both arguments just in place, one after the other, so that the torn formula becomes one again. In other words
Of course, you can cascade $(variables) in combination with $(NiceNumber) for example
And here comes the code ... thank you for reading until here
// Use format like this: $(NiceNumber(yourvalue)) // no spaces
SET NiceNumber = Dual(Replace(Replace(Replace(Replace(
If(Fabs($1) > 1e9, Num($1/1e9+1e-13, '#$(ThousandSep)##0$(DecimalSep)00 B', DecimalSep, ThousandSep)
,If(Fabs($1) > 1e8, Num($1/1e6+1e-13, '##0$(DecimalSep)0 M' , DecimalSep, '')
,If(Fabs($1) > 1e6, Num($1/1e6+1e-13, '##0$(DecimalSep)00 M', DecimalSep, '')
,If(Fabs($1) > 1e5, Num($1/1e3+1e-13, '##0$(DecimalSep)0 k' , DecimalSep, '')
,If(Fabs($1) > 1e3, Num($1/1e3+1e-13, '##0$(DecimalSep)00 k', DecimalSep, '')
, Num($1, '#$(ThousandSep)##0$(DecimalSep)00 ', DecimalSep, ThousandSep)
))))),'00 ',' '),'0 ',' '), '$(DecimalSep) ', ' '),' ',''), $1);
LET NiceNumber2 = Replace(NiceNumber,'$1', '$1,$2');
LET NiceNumber3 = Replace(NiceNumber,'$1', '$1,$2,$3')