Currency conversion expression help please

[#1]

Hi All

I'm desperate for some help with an expression where I'm pretty sure I need an aggregate and set analysis.  I've been through (I think) every other thread even vaguely linked to currency conversions, but so far I've got nothing that works.

I have revenue by client and country, all pulled from the data warehouse in local currency.  And thanks to the community, figured out how to enable multi-currency in the script, which allows the end user to select the currency they want to see the dashboard in.  So far so good.

What I end up with in the final data table is:

Month

Year

Client

Amount_Local

Source_Currency_Code

Target_Currency_Code

FX_Rate

Where the FX_Rate is the conversion factor from the source to the target.

I'm trying to show a cross tab, with revenue by client by month.  I thought this would work (with Client as the dimension)

=Sum(DISTINCT{$<Month={'Feb'},Year_YYYY={$(=vMaxYear)}>}Amount_Local*FX_Rate)

but no dice, because of course, not specifying the target currency code - and the DISTINCT is a problem.  It looked pretty good on the client I isolated to check, but fell in a heap the minute I tried another client.  And the second I take the DISTINCT out, I get the Amount multiplied by 9 (the number of target currency codes).

Then I tried this:

=Sum(Aggr(Sum({$<Month={'Feb'},Year_YYYY={$(=vMaxYear)}>}Amount_Local*FX_Rate),Target_Currency_Code))

but still not working.

I'm sure someone's going to look at this and tell me there's a REALLY easy solution, but right now, I feel like pulling my hair out and/or take up drinking gin in the office.

Any help you can offer would be extremely gratefully received.

Lisa

[roger_stone]

Can you anonymise the clients and post a bit of sample data?

Gin in the office....hmm...

[settu_periasamy]

Hi,

May be, if the Target Currency selected by the End user,

Try like..

=Sum({$<Month={'Feb'},Year_YYYY={$(=vMaxYear)}>}Amount_Local)*

Only({<Month={'Feb'},Year_YYYY={$(=vMaxYear)},Target_Currency_Code={'$(=Target_Currency_Code)'}>}FX_Rate)

[#4 · Accepted solution]

I believe, your data model looks bad here. Don't complicate the data model and keep it simple as much as possible.

Please keep Fact Table as it and add the another table to handle the currency conversion. Let assume your local curreny is in US Dollars

FactData:

LOAD

     Month,

     Year,

     Client,

     Amount_Local,

     'USD' AS Source_Currency

From FactTable.qvd (qvd);

So in the fact table you always have USD value and if you want to convert in to any other currency value or keep USD as default.

Simply add the another table have Source_Currency, Target_Currency and FX_Rate and this table associate on the Source_Currency to FactData table.

CurrencyDimension:

LOAD * INLINE [

Source_Currency, Target_Currency , FX_Rate

USD, USD, 1

USD, GBP, 0.75

USD, AUD, 1.10

];

So you don't worry about either DISTINCT or AGGR function. But you always select alteast one value on the Target_Currency to make your expression work

Sum({$<Month={'Feb'},Year_YYYY={$(=vMaxYear)}>}Amount_Local*FX_Rate)

or smartly change the FX_Rate value based on selections by default to USD.

Sum({$<Month={'Feb'},Year_YYYY={$(=vMaxYear)}>}Amount_Local) * IF( getselectedcount(Target_Currency )=1 , FX_Rate , only({1<Target_Currency={"USD"}>}FX_Rate)

[#5]

Thanks Dathu, that's exactly what my data model is doing - I pull the base client data in local currency into the QVW, then join another file for the currency conversions.  So great to know that's the right approach.

And I also have "always one selected value" active in the target currency list box.

But I just can't get the expression to sum the revenue by client by month working in a table.

[#6]

Guess what?  Dathu was right!

Someone (and by someone, I mean me) put the conversion process in the wrong place in the script - so instead of just joining the currencies to the fact table, it was duplicating each fact once for every currency conversion option I was providing.

Because I've got transactions which I'm linking to financials to ensure they reconcile, I build a key table to eliminate synthetic keys.  I was adding the currency conversion after loading in the transactions but before building the key table, and that was the issue.

I've moved the currency conversion to AFTER the key table build, and it all works.

Thanks for coming back with responses on this - much appreciated, especially since it made me look properly at the script!