6 Replies Latest reply: Apr 29, 2015 10:09 AM by LUCIANA SILVEIRA RSS

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

    Artjoms Tukums

      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
          Anatoly Pyatygo

          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
              Artjoms Tukums

              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
                  Neil Miller

                  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
                      John Witherspoon

                      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.