topic Currency in QlikView

Currency

Wed, 18 Aug 2010 15:07:54 GMT

Hi guys.

I got a document that looks like such:

Now what want to do is whenever someone selects USD in the currency box, That the amount shows in USD only. When some clicks on AUD then it displays the sales in AUD amounts.

All the currencies need to be multiplied by certain values in order to get the right amount. I have those values but i can't seem to work it out in the script. I have managed to do it seperatly in the table but in needs to be done in the script so that when the selected currency is made then the whole document changes according to that.

Can anyone help me please?

Thanks in advanced

Currency

Wed, 18 Aug 2010 15:41:03 GMT

I managed to do an inline statement and mapping it like this:

//Mapping Currency

CurrencyMappingTable:

Mapping

LOAD * INLINE [

F1, F2

USD, *1.0000

CAD, *1.0187

FRF, *1.0000

AUD, *1.0908

GBP, *0.6650

DEM, *1.0000

EUR, *0.7399

];

applymap('CurrencyMappingTable',LocalCurrency) as Currency,

blah blah blah

but the problem is it loads like this:

now my question is, how do load it so it displays USD,AUD,GBP, etc.?

Currency

johnw — Thu, 19 Aug 2010 00:07:04 GMT

This may be a little more than what you're looking for, but here's an example based on the assumption that you'll have a different exchange rate for each date. It takes the table of sales, and creates a new table with rows for every sale in every currency. Make a list box that forces you to select one and only one currency, and sales, at the data level, will be in that currency. No multiplication or any other chart tricks. The entire document is affected. If you want to see a particular currency other than the one selected, you can override it with set analysis on a chart-by-chart basis.

Currency

Thu, 19 Aug 2010 07:36:40 GMT

Is there a way to make it so that if dollars are select than $ is shown, and when Pounds are selected £ is shown, etc?

Thanks for the help

Currency

Thu, 19 Aug 2010 08:53:49 GMT

no update?

Currency

Thu, 19 Aug 2010 09:58:58 GMT

anyone?

Currency

Thu, 19 Aug 2010 12:21:30 GMT

please.....?

Currency

nathanfurby — Thu, 19 Aug 2010 14:55:54 GMT

You're probably talking Macro territory here. Take a look at this post for some pointers:

http://community.qlik.com/forums/t/30034.aspx

Afraid I can't fill in the blanks

Currency

Thu, 19 Aug 2010 15:03:39 GMT

Hi Nathan.

Thanks for the reply. The link was a little helpful but not really. could you maybe explain how i can achieve wat i want?

thanks

Currency

nathanfurby — Thu, 19 Aug 2010 15:15:30 GMT

Like I said, I don't have the answers as I have never done this before. But I imagine how it would work is to create a macro that modifies the number format properties (as in the example) and call it on the currency field event trigger.

Because the documentation is completely useless I wouldn't know which properties to set etc.

Currency

Thu, 19 Aug 2010 15:21:27 GMT

Okay cool. Thanks for the info Nathan. Much appreciated

Currency

nathanfurby — Thu, 19 Aug 2010 15:28:17 GMT

No worries - make sure you post the solution here when you crack it!

Currency

Thu, 19 Aug 2010 15:42:00 GMT

IF i crack it lol

Currency

johnw — Thu, 19 Aug 2010 17:52:23 GMT


Blaze101 wrote:Is there a way to make it so that if dollars are select than $ is shown, and when Pounds are selected £ is shown, etc?

One approach to a dynamic currency format is to build a table:

Currencies:
LOAD * INLINE [
Currency: Currency Format
RUB: #,##0.00 Rubles;(#,##0.00 Rubles)
USD: $#,##0.00;($#,##0.00)
EUR: €#,##0.00;(€#,##0.00)
] (delimiter is ':');

Then just use num(...,"Currency Format") anywhere you need the dynamic format. This should work in most places, but doesn't work in a straight table. However, I don't ever use straight tables in actual applications, so I don't consider this much of a drawback. I think it's better than a macro solution - more clear what's happening, and doesn't have all the drawbacks of macros.

See attached for updated example.

Currency

nathanfurby — Thu, 19 Aug 2010 18:22:56 GMT

Very nice John.

Currency

johnw — Thu, 19 Aug 2010 21:20:16 GMT

Example expanded with correct use of comma and period in Euros. Not sure what Russian Rubles use, so left as is.

Currencies:
LOAD * INLINE [
Currency: Format: Thousand: Decimal
RUB: #,##0.00 Rubles;(#,##0.00 Rubles): , : .
USD: $#,##0.00;($#,##0.00) : , : .
EUR: € #.##0,00;-€ #.##0,00 : . : ,
] (delimiter is ':');

money(sum(Amount),only(Format),only(Decimal),only(Thousand))

Currency

johnw — Thu, 19 Aug 2010 21:46:58 GMT

For some reason, the web site isn't letting me edit the entry. It lets me do everything and hit post, but then nothing happens. So... new entry.

Created custom money "function" to simplify use in charts.

LET money = 'money($1,only(Format),only(Decimal),only(Thousand))';

$(money(sum(Amount)))

Currency

dvasseur — Fri, 29 Apr 2011 16:33:13 GMT

It's an old thread but same problem so...

I've used the same trick than John has suggested and it's working fine! The only drawback is when you export your chart into Excel. Values are exported as text and not as numbers, so the file is useless (if you export to Excel, most of the time it's to do additional calculations based on QV values).

I'm using QV10 SR2.

Any idea ?

Currency

johnw — Fri, 29 Apr 2011 17:22:33 GMT


David Vasseur wrote:I've used the same trick than John has suggested and it's working fine! The only drawback is when you export your chart into Excel. Values are exported as text and not as numbers, so the file is useless (if you export to Excel, most of the time it's to do additional calculations based on QV values).

I'm not sure what you're referring to. What chart are you exporting? What is coming out wrong? I see that the dates are wrong, as they're exported as a number, but that's the opposite of what you're saying is wrong. I'm using 9SR6 and 10SR1, though, so I suppose it's possible there's a version difference.

This is also older than my most recent modifications to the example. First I'll post what I'd use if the exchange rates are changed at regular intervals - daily, weekly, whatever.

Currency

johnw — Fri, 29 Apr 2011 17:25:39 GMT

And here's what I came up with for exchange rates that are updated at irregular intervals.