Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jjordaan
Partner - Specialist
Partner - Specialist

Variable in the string variable MoneyFormat

Hello everyone,

I'm making a dashboard linked to a NAV database.

Within this NAV database there is setting ocal currency with the corresponding symbol. I have putted this currency symbol in a variable.

Now I want this variable added to the moneyformat in the script.

something like

SET MoneyFormat ='$(vCurrSymbol) & #, ## 0.00; -$(vCurrSymbol) & #, ## 0.00";

Only I do not get the right result. The variable is displayed as text.

Can anyone help me?

Thank you very much

1 Solution

Accepted Solutions
whiteline
Master II
Master II

When you load SaleslineAmount, you can use instead:

dual(CurrencyCodesSymbol & ' ' & num(SaleslineAmount, '#,##0.00', '.' , ','), SaleslineAmount) as SaleslineAmount,

but field CurrencyCodesSymbol should be in the same source table.

View solution in original post

13 Replies
whiteline
Master II
Master II

Everything works fine. Just correct the last quote.

SET MoneyFormat ='$(vCurrSymbol) & #, ## 0.00; -$(vCurrSymbol) & #, ## 0.00';

jjordaan
Partner - Specialist
Partner - Specialist
Author

Whiteline,

Thanks for your reply.

I tried your example but then I get also the wrong result.

See the screenhot attachment.

whiteline
Master II
Master II

Strange. I just changed my MoneyFormat on yours and defined vCurrSymbol in script.

Are you using this expression with $(vCurrSymbol) to make a dynamic text format ?

If so, you have to change the entire MoneyFormat value not just vCurrSymbol.

MoneyFormat is just a string, it seems that QV doesn't recalculate it.

jjordaan
Partner - Specialist
Partner - Specialist
Author

whiteline,

I created the variable vCurrSymbol like this:

LET vCurrSymbol = '=CurrencyCodesSymbol';

I copied your string into my script?

Thx for the help

whiteline
Master II
Master II

what is '=CurrencyCodesSymbol' ???

You should use something like this:

SET vCurrSymbol = 'AU';

jjordaan
Partner - Specialist
Partner - Specialist
Author

I added an example how I do it.

Thx again

whiteline
Master II
Master II

You can't do it that way.

If you want to change the format of some field depending on value of another field use dual() function.

In this case you can set up text representation and numeric value of your field separately.

Try this expression for sales:

=dual(CurrencyCodesSymbol & ' ' & num(Sum(SaleslineAmount), '#,##0.00', '.' , ','), Sum(SaleslineAmount))

jjordaan
Partner - Specialist
Partner - Specialist
Author

Your solution works.

Thx for that

The onley thing is I have to do this for al the expressions and I have a lot expressions

whiteline
Master II
Master II

You can use the same aproach in the load script.