QlikView formula to get the Sense style Nice Number format

ChristofSchwarz — Wed, 06 May 2015 16:56:49 GMT

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.

Then I wrote the below complex formula as a nested if-decision tree with Num() formattings as a traditional QlikView formula.
I defined it as a $()-variable with one argument, so you can use it anywhere in your app with this syntax $(NiceNumber(yourvalue))
... except when one thing happens! ... if your value is another formula and inside this formula there is a comma (",") then this $(NiceNumber) will no longer work. The reason is that it treats the part after the comma as a 2nd argument and I couldn't find any way to escape that formula to be treated by $(NiceNumber()) as one argument

Examples:

$(NiceNumber(Sum(Sales))) ... works fine
$(NiceNumber(Sum(Pow(Sales,2))) ... doesn't work, but I have a workaround.

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

$(NiceNumber2(Sum(Pow(Sales,2))) ... works fine
$(NiceNumber3(Avg(Aggr(Sum(Sales),Dim1,Dim2)))) .... works fine (now having 3 arguments due to the two commas)
You can continue create NiceNumber4 ... NiceNumberN in the same principle as below if needed.

Of course, you can cascade $(variables) in combination with $(NiceNumber) for example

SET @Sales = Sum(Sales);
Use: $(NiceNumber($(@Sales))) ... works fine

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

topic QlikView formula to get the Sense style Nice Number format in QlikView

QlikView formula to get the Sense style Nice Number format