cancel
Showing results for 
Search instead for 
Did you mean: 

Translating or Converting Units or Volumes

T_Kress
New Contributor II

Often global companies may report a metric like units sold or volumes in a stat account.  But that unit of measure may vary by region or country.  Below is one option for handling the conversion of stat accounts like units sold or volumes.

 

For example:

  • Metric Ton to US Ton
  • Kilograms to Pounds
  • Liters to Gallons
  • Etc

 

Here are some high level ways to tackle this in an application. 

 

Considerations:

  • Entities are tagged with currency already
  • Normal Revenue/Expense account types will naturally get “AverageRate” table PVA method (all else equal)
  • Normal Assets/Liability account types will naturally get “ClosingRate” table VAL method (all else equal)
  • You need to identify which accounts (aka UNITSSOLD) need a translation rate table that differs
  • You need to identify which entities are sending which volumes to drive translation to use different rate table

 

Your conversion rates do not vary over time so you really just need to store these rates once in the application for all time periods:

  • KilogramRate (store conversion rate to go from kilograms to pounds)
  • MetricRate (store conversion rate to go from metric tons to US tons)
  • LiterRate (store conversion rate to go from liters to gallons

 

There are a few different options for storing these conversion rates:

  1. LOOKUP table under transformation rules section
    1. Preferred method
    2. Stores once in app that will come out with app extract file
    3. Can be referenced in TransformText api to grab rates during conversion
  2. Custom table in the app
    1. Stored once in app but must be recreated with each app copy or new app
  3. FX rate tables pertaining to these elements
    1. Leverages standard place for rate but since these rates do not vary over time, you would have to load these conversion rates to all time periods into eternity
  4. Account that is balance recurring that stores these conversions in E#None:C#None to be used across all entities
    1. Leverages standard place but again, having to store these rates by time period into eternity
    2. Does not come out with app extract since it is data

 

Set up accounts as follows:

  • Tag various volume or units accounts as type “Revenue” so that they will get translation
  • Use Text# on account to drive the rate table being used so that it does not use “AverageRate” but instead goes to one of the 3 new rate tables that you set up as:  LOOKUP table (preferred method), Custom table, FX Rate table or Account

 

Set up entity as follows:

  • Use Text# on entity to differentiate what unit of measure is being sent
    • This may not be needed if you can direct into different accounts upon load

 

Set up cube as follows:

  • Translation Algorithm Type = Standard Using Business Rule for FX Rates
  • In this Finance BR just isolate list of accounts that need other than standard translation, e.g. focus on handful of volume accounts that should use one of the new unit conversion FX rate tables
  • All else can be standard translation (all else equal)
Teresa C. Kress
Sr Delivery Manager Partner Enablement | OneStream Software
0 REPLIES 0
Please sign in! T_Kress