Trouble applying thousand separators during reload

[Vegar]

I have multiple fields in many application that contains money values. I want to standardize the number and money  formats by defining a default setup of our System Variables

• ThousandSep
• DecimalSep
• MoneyFormat
• MoneyThousandSep
• MoneyDecimalSep

When running the script I only get to apply the decimal separators and the currency symbol, I don't get the thousand separators be applied on money() nor num() formating. 

I don't know if there is something that I've missed, missunderstood or if this is a bug.

I use this script.

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';

Source:
LOAD 
	num(round(rand() * 10000, '0.001')) as Value
AutoGenerate 10;

LOAD	
	Value,
	money(Value)					as Money,
	text(money(Value)) 				as Money_text,
	money(Value, '$(MoneyFormat)') 	as Money_moneyformat,
	num(Value)						as Num
Resident Source;

drop table Source;

I expect my new field Money and Money_moneyformat to both have this format: '# ##0,00 €;-# ##0,00 €'

But when i run my script the thousand separator is not applied to the Money field, only the decimal separator and the currency symbol, but the Money_moneyformat is correct. 

However, if I wrap text() around my Money calculation I do get the correct format, but the value is stored as a string. This implies that the format is there during the script reload, but is lost before being presented in the application.

See output of my script below (with comments)

Do you guys get the same behavior from the thousand separators if you run my script? Do you know how to fix it so that the separator is applied automatically during reloads ? Or do you know any documentation saying this is how it supposed be?

[Anushkaa]

Hi, you can use Money#(Money(Value)) as Money. It will give same result as Money_moneyformat field.

[rwunderlich]

I get the same results as you. I believe this is a long outstanding bug (from QV days) that I never dug into until your post.  But I do recall having to use the Money(value,format) form and never understood why. 

it looks like the issue is when default format is used in Money(), i.e., no second parameter, spaces are removed from the MoneyFormat format string when it is stored with the field.  In my "Money" field results the space between the last digit and the euro symbol is missing. It doesn't appear that way in your image, maybe you can double check. 

Money() does not create an explicit dual, but rather stores the format with the field and applies it at display time. Looking at both fields using QSDA Pro, I can see that the format mask stored with the "Money" field is missing spaces whereas the "Money_moneyformat" field does have spaces. 

@Anushkaa suggestion of Money#(Money(Value)) is a workaround in that it creates an explicit dual at the cost of increasing the storage required for the field. 

https://qlikviewcookbook.com/2017/03/dual-storage-vs-dual-behavior/

The issue is probably worth pursuing as a bug or a doc clarification. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Vegar]

Thanks for looking into this @rwunderlich .

Something have happened with the script when copying it into a community post and then back into an Qlik application. When I use my original script I get spacing between the value and € sign, but when I use the code snipplet I posted in this thread I get the same result as you. 

However I don't think this issue is limited to blanks. I get this problem even if I don't use blanks as separator. Using the setup as shown below with _ as thousand separator still leaves out the separator when using money()

SET ThousandSep='_';
SET DecimalSep=',';
SET MoneyFormat='#_##0,00 €;-#_##0,00 €';
SET MoneyThousandSep='_';
SET MoneyDecimalSep=',';

I am attaching a qlik sense qvf file with the correct blanks that separates the number and the € when using money().