Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
phultman
Contributor II
Contributor II

Money format issues (both app and export)

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:

1.PNG

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

3.PNG


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

2.PNG


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

4.PNG


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

5.PNG


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

6.PNG


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


/Peter

3 Replies
praveenkumar_ma
Partner - Creator
Partner - Creator

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
Contributor II
Contributor II
Author

Hi Praveen,

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

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

/Peter

pierreguss1
Partner - Contributor II
Partner - Contributor II

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

pierreguss1_0-1597332376715.png

 

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 🙂