Related to the above: I am trying to replicate the result from

select averagerate from consolidatedexchangerate where fromcurrency = <sub_currency> and postingperiod = <posting_period>

that is, compute the average rate for a subsidiary for a posting period, not using the above table. Right now I have:

select sum(abs(tal.amount) * cr.exchangerate) / sum(abs(tal.amount))
from transactionaccountingline tal
  inner join transaction t on t.id = tal.transaction
  inner join transactionline tl on tl.id = tal.transactionline and tl.transaction = tal.transaction
  inner join currencyrate cr on cr.effectivedate = t.trandate and cr.transactioncurrency = t.currency
  inner join account a on a.id = tal.account
where t.postingperiod = '<posting_period>'
  and t.currency = '<subsidiary_currency>'
  and tl.subsidiary = '<subsidiary>'
  and cr.basecurrency = '<parent_basecurrency>'
  and a.generalrate = 'AVERAGE'

I am close right up to 3 significant figures, but I think I should be exactly right. I must not be getting the right subset of transaction accounting lines. What am I doing wrong here.

@Nim Sadeh Could I ask why you'd want to compute the average rate for the sub yourself, and not use the average rate stored for the sub in the

consolidatedexchangerate

table?

Verification - I am trying to ensure that the consolidated report is up to date/accurate

and you're concerned there is like a rounding issue?

Well I’d like to be able to “show my work”, I followed the documentation for how the average consolidated exchange rate is computed, and I am getting a different result than what NetSuite has auto-computed

I'm not sold on the use of absolute value in your query. Trying to digest the whole thing

Yea, I see your point. It’s dramatically less accurate without the absolute value, which I somewhat expected. Although the NetSuite docs don’t mention absolute value (since their example only deals with positive values), you’d have to use the magnitude of the transaction instead of the actual net amount. If you had a period where all the transaction added up to 0, for example, you’d be dividing over zero if you didn’t use the absolute value

Yeah, I'm trying to wrap my head around this one right now 🤔

the only thing I can think of is a potential NetSuite bug where if someone changes a transactions date after the end of the month they may not update their consolidated rate? For example: 1. transaction is made on 8/15 with a transaction date of 8/15 2. (hypothetically) on 9/1, NetSuite updates the consolidatedexchangerate table 3. On 9/2, someone goes and changes the transaction date to 8/17. It now uses a different FX rate, so the average rate will change as well 4. NS doesn’t update the consolidated table to reflect this? It seems unlikely

Well that could very be a bug that exists. I've seen plenty of caching issues like that. But probably not the culprit here

Yea, it could, but it wouldn’t change the output of the query

one more thing

I had

and t.postingbook = '1'

in earlier versions of this query and the results didn’t change

do you have access to a sandbox? I'm wondering what Netsuite does when a transaction is dated prior to the period start date...my gut feel is it would use the rate from the 1st

Overall, the query seems right to me. I would be curious if you've tested this against multiple different subsidiaries and periods and if it's always off by a small amount or if it's sometimes right

the query needs to join to the subsidiaries table Then your join to the currency table should be using the base currency of the subsidiary, rather than the transaction currency

I think he’s just hard coding the sub currency in this case though

yes but then he will be missing all transactions that aren't in subsidiary currency

Ohhh I see what you’re saying

@Jon Kears it appears all transactions have a transaction date on or after the start of the period

When applying consolidated exchange rates, you don't care about transaction currency. It's possible in Netsuite to force a currency rate on a transaction to represent (e.g.) the rate the bank gave you rather than the Netsuite imported exchange rate. In this situation, all that matters for the calc is what is the net movement in the base currency of the subsidiary. So I think it's pretty logical assumption that Netsuite does the same when calculating the rate too. So you want to get all the movements in base currency only (which you're getting, using the tal.amount field). That value is expressed in the base currency of the subsidiary in question. So in order to convert, you need to work out the rate from subsidiary currency to the parent subsidiary currency. So you need a join from transactionline to subsidiary, then you need to join to currencyrate based

on subsidiary.basecurrency = cr.transactioncurrency AND <parent_basecurrency> = cr.basecurrency AND t.trandate = cr.creffectivedate

Now this might or might not matter depending on the nature of transactions you have in this subsidiary. I suspect Netsuite is doing something with rounding, and something with null values, as if (for example) you have no transactions at all then it forces a consolidated exchange rate of the last day of the period.

Yea due to the nature of these transactions, this join results in the same number. Can you expand a bit on rounding? I did notice that my result has two more figures than NetSuite’s.

One sanity check. You mentioned you are trying to replicate the result from

select averagerate from consolidatedexchangerate where fromcurrency = <sub_currency> and postingperiod = <posting_period>

I think we're really talking about replicating the result from this though:

select averagerate from consolidatedexchangerate where fromsubsidiary = <subsidiary> and tosubsidiary = <parent> and postingperiod = <posting_period>

In theory, this could get you the same record, but just want to confirm

the first gets me three records (from subsidiary to itself, from sub to parent sub, from sub to parent). The latter gets me a single record. The rates are as expected: the latter query has value X, the two last records from the first query also have X, and the first record from the first query has 1.