Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jjordaan
Valued Contributor

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

Re: Variable in the string variable MoneyFormat

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.

13 Replies
whiteline
Honored Contributor II

Re: Variable in the string variable MoneyFormat

Everything works fine. Just correct the last quote.

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

jjordaan
Valued Contributor

Re: Variable in the string variable MoneyFormat

Whiteline,

Thanks for your reply.

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

See the screenhot attachment.

whiteline
Honored Contributor II

Re: Variable in the string variable MoneyFormat

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
Valued Contributor

Re: Variable in the string variable MoneyFormat

whiteline,

I created the variable vCurrSymbol like this:

LET vCurrSymbol = '=CurrencyCodesSymbol';

I copied your string into my script?

Thx for the help

whiteline
Honored Contributor II

Re: Variable in the string variable MoneyFormat

what is '=CurrencyCodesSymbol' ???

You should use something like this:

SET vCurrSymbol = 'AU';

jjordaan
Valued Contributor

Re: Variable in the string variable MoneyFormat

I added an example how I do it.

Thx again

whiteline
Honored Contributor II

Re: Variable in the string variable MoneyFormat

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
Valued Contributor

Re: Variable in the string variable MoneyFormat

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

Re: Variable in the string variable MoneyFormat

You can use the same aproach in the load script.

Community Browser