topic Problem in aggregating data with different currencies in QlikView

Problem in aggregating data with different currencies

Mon, 04 Feb 2013 13:33:12 GMT

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!

Re: Problem in aggregating data with different currencies

hic — Mon, 04 Feb 2013 15:08:16 GMT

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

Re: Problem in aggregating data with different currencies

Tue, 05 Feb 2013 12:19:04 GMT

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

Re: Problem in aggregating data with different currencies

hic — Tue, 05 Feb 2013 13:43:59 GMT

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