NUM formatting master measure

[rlawton1]

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

[JuanGerardo]

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

[rlawton1]

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

[JuanGerardo]

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

[rlawton1]

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?

[JuanGerardo · Accepted solution]

I was wrong, I forgot to include the format 😬

It should be something like this:

Num(Sum(Gross), '#,##0', '.' , ',' )

JG

[rlawton1]

You legend, that's Perfect! 

Thanks for your help!