Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
scw
Former Employee
Former Employee

Custom formatting large sales values in dollars and use "," as 1000 separator when locale settings are "€" for currency and "." for 1000 separator

Hi - I am trying to format large sales sums in multiples of thousands. For e.g. I want a number like 70902300.803 to be displayed as $70,902,300.803. However my locale settings are € for currency and thousand separator that I am using is "." Any ideas how I can do this by creating custom formatting for the measure? I want both values to co-exist in my dashboards.

9 Replies
sunny_talwar

Try this:

Num(70902300.803, '$#,###.###')


Capture.PNG

scw
Former Employee
Former Employee
Author

Hey Sunny - thanks for your response but doesn't seem to work for me. Also I am doing this in QlikSense so I am not sure if the dynamic expression needs to be different. I would think not but just wanted to let you know.

sunny_talwar

What object are you trying to do this in? and is the value coming from a field? May be the value is formatted as text and you need to change it to number format. Just give this a shot:

Num(Num#('70902300.803','##.###'), '$#,###.###')

sunny_talwar

Or may be use the Qlik Sense's formatting:Capture.PNG

sunny_talwar

Capture.PNG

scw
Former Employee
Former Employee
Author

It's actually a measure. A simple sum(sales) function. It's default formatting(or default created by sense) is #.##0,00 €;-#.##0,00 € because the app was made by someone in Europe. I need to change this to dollars and in multiples of 1000.

quriouss
Creator III
Creator III

Just change from:     #.##0,00 €;-#.##0,00 €

to:                           $#,##0.00;-$#,##0.00


scw
Former Employee
Former Employee
Author

Thanks for your responses guys. I found out that the reasons your suggestions aren't working is because my locale settings are set to use € for currency and more importantly "." as 1000 separator. When I use $#,##0.00;-$#,##0.00 instead of #.##0,00 €;-#.##0,00 € the 1000 separator is just not placed at the correct point. So instead of 1,000.12 I get 1000,012. Will I need to make changes to the load script to get values in both formats to co-exist?

sunny_talwar

In you script editor, may be change these if they don't look like these:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';