topic Re: Calculate Amounts in Base Currency Using Rate for the End of Period in QlikView

Calculate Amounts in Base Currency Using Rate for the End of Period

Anonymous — Wed, 25 Aug 2010 12:50:30 GMT

Hi everybody,

I need a special way to calculate amounts in base currency using exchange rates. User can choose a period to analyse Accounts Receivable historical status (Difference between Invoiced amount and received cash). Amounts could be in different currencies. For summary all amount has to be in base currency - USD. There is no problem to calculate USD amount using each transaction date exchange rate (by linking exchange rates through date and currency). But in my case all amounts has to be recalculated to USD using period last date exchange rate.

E.g. user analyses week 17. It ends on 2nd of May year 2010 (2010.05.02). So expressions have to divide amounts in transaction currencies by exchange rates for 2nd of May.

In my case it looks like two Set Analysis statements one within another. But I have no idea how to reach it.

I think, that an independent exchange rates table is needed, so I created it in attached file.

Any thoughts?

Rgds,
AT

Calculate Amounts in Base Currency Using Rate for the End of Period

sparur — Thu, 26 Aug 2010 11:03:52 GMT

Hello, Artjoms.

I don't sure that your task can be resolve only by Set Analysis. So I created some changes in your script and then created a small chart. I use only one Key figures (Receipts Amount). I think you can understand my idea and create other expressions...

see in my example (sheet 2 - RESULT table)

Calculate Amounts in Base Currency Using Rate for the End of Period

Anonymous — Thu, 26 Aug 2010 12:08:36 GMT

Cool solution!

But in this case user can select only whole week period. Selected period can be a month, a week, a year or even a difference between two dates.

Calculate Amounts in Base Currency Using Rate for the End of Period

Thu, 26 Aug 2010 16:54:42 GMT

I was unable to solve your problem, but I did notice a few things that I'd like to point out, in case it helps.

First, your variable, vCurrRateDate, is a date. In your data set, your dates are numbers. In order to use that variable, you want it to be a number, not a date. I made a new variable, vCurrRateNum, with the expression:

=if(max(Cal.Date)>max(total Date),max(total Date),max(Cal.Date))

Then, you are using Only in your denominator when trying to get the currency for each Office/Customer. That isn't working, because the dollar sign expansion is evaluated for the entire chart, not record by record. If you put just the denominator into an expression, you will notice that it returns null for everyone. I believe the only way to handle this portion is to use an if, which is evaluated separately for each record. Here is an expression that I believe gives you the exchange rate to use for each record:

Max({<CurrRates2.Date={"$(#vCurrRateNum)"}>}
 If(CurrRates2.Trans.curr = Trans.curr, [CurrRates2.Curr rate]))

If you put that into an expression by itself, you will get an exchange rate returned, you should verify if it is the correct exhchange rate.

Finally, you can't use a Max (or Only, I believe) inside of another Sum. In order to use nested aggregates, you must use the Aggr function. Here is the correct format, but I don't believe that is giving you the intended answer:

=sum({<Date = {"<=$(=max([Cal.Date]))"}>} [Amount curr Rec]/
 Aggr(
 Max({<CurrRates2.Date={"$(#vCurrRateNum)"}>}
 If(CurrRates2.Trans.curr = Trans.curr, [CurrRates2.Curr rate]))
 , Office, Customer))

Looking at your expression, I think you want to Sum up all of the Rec or Paym values and then divide by the exchange rate. Is there any reason to divide each individual record by the exchange rate? If you sum, then divide, you can get rid of the Aggr. I'm still not sure if this is the result you were looking for, but it looks reasonable:

=sum({<Date = {"<=$(=max([Cal.Date]))"}>} [Amount curr Rec])/
 (Max({<CurrRates2.Date={"$(#vCurrRateNum)"}>}
 If(CurrRates2.Trans.curr = Trans.curr, [CurrRates2.Curr rate]))
 )

Sorry for the long winded reply, but I didn't get that final expression until I finished typing up the other stuff. Take a look at the sample and see if that is what you are looking for.

Calculate Amounts in Base Currency Using Rate for the End of Period

johnw — Sat, 28 Aug 2010 00:12:18 GMT

If I understood the question:

Link the CurrRates2 table by Trans.curr (don't qualify it).

vCurrRateDate = =rangemin(max(Cal.Date),max(total Date))

sum(aggr(sum({<Date={"<=$(=max(Cal.Date))"}>} [Amount curr Rec])
/only({<CurrRates2.Date={$(vCurrRateDate)}>} [CurrRates2.Curr rate])
,Office,Customer,Trans.curr))

sum(aggr(sum({<Date={"<=$(=max(Cal.Date))"}>} [Amount curr Paym])
/only({<CurrRates2.Date={$(vCurrRateDate)}>} [CurrRates2.Curr rate])
,Office,Customer,Trans.curr))

See attached.


NMiller wrote:I think you want to Sum up all of the Rec or Paym values and then divide by the exchange rate. Is there any reason to divide each individual record by the exchange rate?

You can't sum and then divide because there can be multiple currencies involved since our chart doesn't have currency as a dimension. But that doesn't force us to do all the work at the individual record level either. You can aggregate by the chart dimensions PLUS currency, and only do the division once for each unique combination you find. That's what I did above. I suspect it would be faster than doing it at the record level.


NMiller wrote:Sorry for the long winded reply, but I didn't get that final expression until I finished typing up the other stuff. Take a look at the sample and see if that is what you are looking for.

Heh. I should have read all the way through instead of just reading "I was unable to solve your problem" and then redoing most of your work from scratch. The only serious issue I see with your version is that when multiple currencies are involved (customer 3 or the total), you treat everything as rubles since that has the maximum exchange rate. That's why I'm using the aggr(). And the expression can be simplified by making the data model change I mentioned above, but that doesn't affect functionality.

Calculate Amounts in Base Currency Using Rate for the End of Period

Anonymous — Mon, 30 Aug 2010 07:56:32 GMT

Thanx, John,

You understood the issue absolutely correct! That is exactly what I need.

I'm just not so fluently oriented in complicated Set Analysis expressions, but I knew, that you guys are

One again thanx a lot for everybody!!!

Best rgds,
AT

Re: Calculate Amounts in Base Currency Using Rate for the End of Period

Anonymous — Wed, 29 Apr 2015 14:09:41 GMT

Amigo John

Sou do Brasil, poderia me orientar, tenho uma tabela que necessita ser atualizada todo final de mês, ou seja dia 30 ou 31 de cada mês, esta em excel.

E o seguinte tenho um valor po exemplo de R$200,00 desde do ano de 2000, necessito atuliazado ela taxa selic acumulado todo final de mês.

data inicial 01/02/2000 - data final - 31/03/2015, este mesmo processo e feito para valores do ano 2001,2002 e 2003, sendo valores diferentes.

Poderia me ajuda a contruir um qvw, onde possa atualizar este valore automaticamente.

Agradeço seu auxilio desde já