Money Format Problem

[jukah_do]

Hi all,

Something weird is happening and I cannot figure out why. I have two apps with the same Main script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ # ##0,00;-€ # ##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-AT';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mär;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';

Then I have this section (again the same for both apps):

Load*Inline [
City,Spend
Zagreb,11.90
Oslo,123.90
Vienna,1234.90
Bratislava,12345.90
Budapest,123456.90
Berlin,1234567.90
Paris,12345678.90
London,123456789.90
Madrid,1123456789.90
]
(Delimiter is ',');

And when I format as money, the two apps show the number differently:

What's going on with the one on the right side I do not know... Qlik Sense Desktop September 2017, both apps created on the same PC. Can somebody explain what's happening here, this bugs me a lot.

Thanks

[MK9885]

Maybe try this for Money Format in script?

SET MoneyFormat= '€#,##0.00;-€#,##0.00';

[ToniKautto]

I would start with adding a text object and expose the actual formats that are applied in the app, just to confirm what the strings look like.

[ToniKautto]

How would this resolve the symptom? But then the format string would not be aligned with the format separators?

[jukah_do]

Hi Aehman, thanks for responding, but this does not work.

[jukah_do]

Thanks Toni, it appears that the strings are correct:

[MK9885]

Did you reload the app and tested it?

I checked with your format Vs mine and after changing the format in script, using Money as Number formatting in front end gave me correct result.

You'd need to use , after €#,##  with no space after ,

[jukah_do]

Yes, I reloaded the app, but it did not work properly. Anyway, I need the formatting specified in the script € # ##0,00

I also really want to know why the first app works while the second provides wrong formatting even though both apps have the exact same script behind...

[ToniKautto]

There are three format variable in play for money. The separators must align with the format pattern, the reason some thing works in your environment is because your settings are completely different.

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

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

[ToniKautto · Accepted solution]

That implies that ## is interpreted as text instead of format pattern. Possibly this is caused by a logical problem related to that your format pattern has two blank spaces, and the engine is struggling with interpreting which should be thousand separator.

Can you temporarily remove space after € to see if that makes any difference?

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

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

Another option would be if your MoneyThousandSep and the blank in format pattern are actually different characters. You can try to include the separator though the variable to ensure they are the one and same.

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #$(MoneyThousandSep)##0$(MoneyDecimalSep)00;-€ #$(MoneyThousandSep)##0$(MoneyDecimalSep)00';