Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am struggling with NUM formatting. The default values set in the load script are European so '.' for the thousand separator and ',' for the decimal separator, I need to display in UK format.
I have a simple master measure of SUM(Gross), which when added to a chart outputs like this: 728007029,59244
If I change the number formatting to 'Custom' and specify ',' as thousand separator and '.' as decimal separator, the format pattern is '#,##0.00' and it outputs this: 728,007,029.59. If i change the Format Pattern to '#,##0' then i get 728,007,029 which is exactly the format I want.
However I don't want to have to specific custom format each time I add the measure, so I took that format pattern and apply it to the formula e.g. NUM(SUM(Gross),'#,##0'). But when I add the measure to a chart I get: 728007029,590
My ideal result is to specify all the formatting in a master measure, have commas for thousand separators and no decimal places, and but I cant get it to work.
Can anyone see where i'm going wrong?
Thanks
Rob
I was wrong, I forgot to include the format 😬
It should be something like this:
Num(Sum(Gross), '#,##0', '.' , ',' )
JG
Hi @rlawton1 , the default characters for separators are set with system variables:
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
Tipically, these variables along with others are set at the beginning of the reload script (if you create a brand new application, Qlik Sense put in the script the default values for your language).
Change them to your preferences, and reload your application.
JG
I cannot change the defaults as the app needs to be both UK and European compliant. The formatting needs to be in the master measure
Then you can use the Num() function, that allows to specify thousand and decimal separators in your master measure, like Num(Sum(something), '$(varThousandSep)', '$(varDecimalSep)'), and using those variables depending on the chosen language.
JG
They don't need to be switched, this measure will only ever be UK format, so i tried this: NUM(Sum(Gross), ',' , '.')
But i now don't have any decimal but also don't have a thousand separators. It just shows as: 728007030
Any idea what im doing wrong?
I was wrong, I forgot to include the format 😬
It should be something like this:
Num(Sum(Gross), '#,##0', '.' , ',' )
JG
You legend, that's Perfect!
Thanks for your help!