multicurrency calcs in qvw

[#1]

I have high volumes of transactional data (millions) stored in qvds in local currencies prob 80% of which is ukp(My home currency) with the remaining in a mix of Euro,US$ and HK$. ie

Branch,Date,SalesValue
1,trandate,100
2,trandate,100
3,trandate,100

A seperate Qvd stores branch and Location

Branch,Location
1,UK
2,UK
3,EU

and Im aware im going to need a currency conversion dimension.

Assuming I need a pivot Table with 8 currency calculations over millions of rows and a list box to select View currency as per John Witherspoons posted testcurrency example (not sure how to link to it here).

How can I avoid doing a conversioncalculation when the SourceCurrency is the Same as the DestinationCurrency.?

We have already found that loads of if statements in the pivot calcs adversly affects perfromance

ColinR

[#2]

We do that by building a cross rates table where we have each currency converted to each currency, obviously in this table when you have From GBP to GBP the FX Rate is always going to be 1.00 so then you don't have to worry about when you do or don't do the conversion, it's always done.

[#3]

I dont want it always done, just when its required, if i can remove millions of pointless calcs its gonna perform much better than millions of set expressions multiplying by 1

[prieper]

Have not done it, but should be achievable by loading conversion of currencies into e.g. USD. Then you need to select the "From" and the "To" currency as variables and do two conversions: amount into USD and then into the other currency.

HTH
Peter

[#5]

Hi

I am guessing that your only choice then is going to be to execute the conversion in the load script.

I understand that you don't want to calculate where it's not necessary, however, an expression such as Sum(SalesValue * FXRate) will work much faster overall than something like IF(Currency=USD,Sum(SalesValue),Sum(SalesValue*FXRate)), so for me it would still make sense to create the cross table and implement the calculation on every field.

If however, you can work out how to achieve this in the load script then all the better, but I suspect you will then need to be creating fields such as SalesValueUSD, SalesValueGBP, SalesValueEUR etc. If you do this then you will probably have copies of charts and tables, each different chart/table would be set up to show the correct currency as selected using perhaps a button. If you can work this out then it would be the best in terms of end-user performance, but I don't know what impact that would have on the load time, you'll need to benchmark it.

[#6]

Cheers thats an interesting Idea, as I only have a few currencies to worry about ,Maybe even seperate table of converted Values keyed to the transaction.

The charts could be duplicated in each currency on seperate tabs with visibility controlled with a conditional show.

Thinking about it your also probably right about the load of the calcs as im probably going to be doing a calc anyway the additional *FXRate in the expression isnt going to make that much difference.

Its good to explore the options here

ColinR