topic Re: Variable in the string variable MoneyFormat in QlikView

Variable in the string variable MoneyFormat

jjordaan — Tue, 11 Sep 2012 16:24:22 GMT

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

Re: Variable in the string variable MoneyFormat

whiteline — Tue, 11 Sep 2012 18:10:37 GMT

Everything works fine. Just correct the last quote.

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

Re: Variable in the string variable MoneyFormat

jjordaan — Tue, 11 Sep 2012 20:40:34 GMT

Whiteline,

Thanks for your reply.

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

See the screenhot attachment.

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 05:15:31 GMT

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.

Re: Variable in the string variable MoneyFormat

jjordaan — Wed, 12 Sep 2012 07:11:42 GMT

whiteline,

I created the variable vCurrSymbol like this:

LET vCurrSymbol = '=CurrencyCodesSymbol';

I copied your string into my script?

Thx for the help

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 07:35:40 GMT

what is '=CurrencyCodesSymbol' ???

You should use something like this:

SET vCurrSymbol = 'AU';

Re: Variable in the string variable MoneyFormat

jjordaan — Wed, 12 Sep 2012 07:55:49 GMT

I added an example how I do it.

Thx again

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 08:04:45 GMT

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))

Re: Variable in the string variable MoneyFormat

jjordaan — Wed, 12 Sep 2012 08:30:50 GMT

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

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 09:54:30 GMT

You can use the same aproach in the load script.

Re: Variable in the string variable MoneyFormat

jjordaan — Wed, 12 Sep 2012 10:04:00 GMT

Sorry for my misunderstanding, I'm not working with QV for that long but I don't see how I can do that

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 10:13:15 GMT

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.

Re: Variable in the string variable MoneyFormat

jjordaan — Wed, 12 Sep 2012 11:54:19 GMT

Thank you very much for your help

Re: Variable in the string variable MoneyFormat

whiteline — Wed, 12 Sep 2012 16:43:27 GMT

This trick with text representation works not only with tables but also with charts (values on data point and so on).