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

[scw]

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.

[sunny_talwar]

Try this:

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

[scw]

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:

[sunny_talwar]

[scw]

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]

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

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

[scw]

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