Avg(ExRATE)

[#1]

Hi

I have the following code:

sum(if(curr='zar',amt)) / avg(if(curr='zar',exRate))

+

sum(if(curr='EUR',amt)) / avg(if(curr='EUR',exRate))

+

...

+

...

I can get the result I want using the above, however it is not practical, as I have to add to the code whenever a new company (with a new currency) is added to the system.

Is there a way around this?

I have added a sample attached...

thanks a mil!

[Gysbert_Wassenaar]

You should probably create an exchange rate table like explained in this blog post:

Creating a Date Interval from a Single Date

And then use intervalmatch to match the dates with the correct interval for the rates. See this document for more information: http://community.qlik.com/docs/DOC-4310

See this discussion for a similar problem

[#3]

Thanks - but i have an exchange rate table.

The initial objective was to take the average exchange rate of the period selected. Which i have done, and the results are 100%.. however that was a single currency.

Now that there are multiple currencies, i need to group the transactions by currency, and also the exchange rate table (by currency).. then ADD them each separately...

Check the sample files attached