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?
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?
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:
Mapping Load distinct client_currency, exchange_rate from [Exchange rates]
Mapping Load distinct billing_code, client_currency from [projects]
Load * from projects;
Applymap('ExchangeRates',Applymap('Currencies',billing_code),null()) as exchange_rate
Then you can use sum(activity_revenue/exchange_rate)