topic Re: Money format issues (both app and export) in App Development

Money format issues (both app and export)

phultman — Wed, 13 Jun 2018 09:20:21 GMT

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

Re: Money format issues (both app and export)

praveenkumar_ma — Thu, 14 Jun 2018 10:46:44 GMT

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

Re: Money format issues (both app and export)

phultman — Thu, 14 Jun 2018 11:13:13 GMT

Hi Praveen,

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

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

/Peter

Re: Money format issues (both app and export)

pierreguss1 — Thu, 13 Aug 2020 15:37:27 GMT

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 🙂