Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

scw
New Contributor III

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
MVP
MVP

Re: Custom formatting large dollar values

Try this:

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


Capture.PNG

scw
New Contributor III

Re: Custom formatting large dollar values

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.

MVP
MVP

Re: Custom formatting large dollar values

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','##.###'), '$#,###.###')

MVP
MVP

Re: Custom formatting large dollar values

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

MVP
MVP

Re: Custom formatting large dollar values

Capture.PNG

scw
New Contributor III

Re: Custom formatting large dollar values

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

Re: Custom formatting large dollar values

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

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


scw
New Contributor III

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

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?

MVP
MVP

Re: Custom formatting large dollar values

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