topic Re: Avg(ExRATE) in QlikView

Avg(ExRATE)

Tue, 20 Aug 2013 09:39:18 GMT

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!

Re: Avg(ExRATE)

Gysbert_Wassenaar — Tue, 20 Aug 2013 18:01:42 GMT

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

Re: Avg(ExRATE)

Wed, 21 Aug 2013 11:01:08 GMT

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