Money format issues (both app and export)

[phultman]

I am struggling to get the money format correct in my apps.

In the script I have the following settings:

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 kr;-# ##0,00 kr';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

The measure I present is coded like this:

Money(Sum(count*netprice))

In a pivot table it looks correct:

But when exporting to Excel it looks like this (missing spaces):

In a bar chart it looks like this (spaces missing):

And when exporting to Excel I get this (spaces still missing):

If I change the number format in my bar chart from "auto" to "currency" I get this:

And when exporting to Excel I get this (values are correct but presentation is divided by 1000):

I would be very grateful if someone could explain to me what is going on and how to get it right

/Peter

[praveenkumar_ma]

Hi Peter,

Check the ThousandSep Once in ur Script , i am seeing Blanks . compare with Default Script down ..

your Script has Blank Settings in Thousandsep

and MoneyThousandSep

and

MoneyDeccimalSep should be '.';

in your case it is ',' ;

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 kr;-# ##0,00 kr';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

Normal Default Script :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';

This might be Helpful to process furthur...

Thanks

PM

[phultman]

Hi Praveen,

Thanks, put It depends on what locale you are using.

I want to have Swedish notation. Hence space and ','.

/Peter

[pierreguss1]

Hello

I'm facing a similar issue, easy to reproduce with an inline load of two rows

On the top, Qlik Sense

Below, the extract in Excel

The problem comes from the currency format

Column 1 : # ##0,00 €;-# ##0,00 €

Column2 : # ##0,00€;-# ##0,00€

Column3 : # ##0 €;-# ##0 € => problem

Column4 :  # ##0€;-# ##0€

I do not know if the problem is also linked to the currency format in Excel settings.

Anyway, it looks like changing the currency format can solve the issue

Hope it helps... 2 years later 🙂