Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
rlawton1
Contributor III
Contributor III

NUM formatting master measure

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

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
JuanGerardo
Partner - Specialist
Partner - Specialist

I was wrong, I forgot to include the format 😬

It should be something like this:

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

 

JG

View solution in original post

6 Replies
JuanGerardo
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

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
Partner - Specialist
Partner - Specialist

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
Contributor III
Contributor III
Author

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
Partner - Specialist
Partner - Specialist

I was wrong, I forgot to include the format 😬

It should be something like this:

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

 

JG

rlawton1
Contributor III
Contributor III
Author

You legend, that's Perfect! 

Thanks for your help!