Problem in aggregating data with different currencies

[#1]

Hi all,

here is my problem:

I have 3 sources (xls files) with the following fields

1st has project_code and activity_revenue;

2nd has project_code, type of work, and currency;

3rd has currency and exchange_rate.

In a chart which has as dimension "type of work" and as expression "sum(activity_revenue)/exchange_rate" I get the errore "No Data to visualize".

Why?

If I change the expression in "sum(activity_revenue/exchange_rate)" data is showed but the values are wrong.

In a table with "project_code" as dimension and "sum(activity_revenue)/exchange_rate" as expression, data is showed correctly.

Someone can figure this out?

Thanks a lot!

[hic]

In principle, the "sum(activity_revenue/exchange_rate)" should be the right one. Then QlikView will assign the exchange rate to each activity_revenue before summing them.

The expression "sum(activity_revenue)/exchange_rate" will work only if there is just one possible exchange_rate. If there are several, QlikView will evaluate this as "sum(activity_revenue)/Only(exchange_rate)", i.e. the denominator will be NULL.

However, your data model only has one key: project_code. So if one project_code has several currencies (e.g. different ones for different types of work) then your data model is insufficient. You need a second key to link activity_revenue and exchange_rate.

Can you post a file?

HIC

[#3]

Thanks Henric,

I tried to apply what you wrote,

the problem is that

sum(activity_revenue/exchange_rate) returns clearly wrong results, and I cant fugure out why (screnshot 2)

sum(activity_revenue)/exchange_rate as you say, works only if one exchange rate only is possible

I attached the structure.

Do you have any idea about how can I handle it?

Maybe with variables?

Because I don't have other keys to add...

[hic]

I see what happens now. A single billing code can link several times to the same client_currency so the Sum() function will loop over all combinations.

Move the exchange rate into the activity table using the following script:

ExchangeRates:

Mapping Load distinct client_currency, exchange_rate from [Exchange rates]

Currencies:

Mapping Load distinct billing_code, client_currency from [projects]

Projects:

Load * from projects;

Activity:

Load *,

          Applymap('ExchangeRates',Applymap('Currencies',billing_code),null()) as exchange_rate

          from activity;

Then you can use sum(activity_revenue/exchange_rate)

HIC