The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

WillVitale's avatar
WillVitale
Contributor II
3 years ago
Solved

Inverse Currency Rate

Hello everyone, I'm making a cubeview and want to put in a formula to show the inverse of our currency rates. Is there a formula I can do like using a get data cell or something similar that could w...
Cube View
Reports
  • OS_Pizza's avatar
    OS_Pizza
    3 years ago

    WillVitaleI am not sure if you are looking to display inverse rate in the cube view cell. If yes, Check below

    1. Create two members under Accounts or UD8 . That will be your choice of dimension. 

    I am using Account here .Make sure to put the account/FormulaType as DynamicCalc

    2. Under Formula - Use the below code for AVGRate. Simillarly create the same for EOPRate (api.FxRates.GetFxRateTypeForAssetLiability().Name)

    Dim fxRateTypeName As String = api.FxRates.GetFxRateTypeForRevenueExp().Name 
Dim timeId As Integer = api.Pov.Time.MemberId 
Dim sourceCurrencyId As Integer = api.Pov.Cons.MemberId
Dim destCurrencyId As Integer = api.Members.GetMemberId(Dimtype.Consolidation.Id, "USD")
Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(fxRateTypeName, timeId, sourceCurrencyId, destCurrencyId)
Return averageRate

    3. Create the Cube View with fist column as AvgRate and then next column as inverseavgrate. (Repaet for EOPRate)

    A#AVGRATE

    GetdataCell("1/A#AVGRATE"):Name("Inverse Average Rate")

                                        RESULT

OS_Pizza's avatar
OS_Pizza
Contributor III
3 years ago

WillVitaleI am not sure if you are looking to display inverse rate in the cube view cell. If yes, Check below

1. Create two members under Accounts or UD8 . That will be your choice of dimension. 

I am using Account here .Make sure to put the account/FormulaType as DynamicCalc

2. Under Formula - Use the below code for AVGRate. Simillarly create the same for EOPRate (api.FxRates.GetFxRateTypeForAssetLiability().Name)

Dim fxRateTypeName As String = api.FxRates.GetFxRateTypeForRevenueExp().Name 
Dim timeId As Integer = api.Pov.Time.MemberId 
Dim sourceCurrencyId As Integer = api.Pov.Cons.MemberId
Dim destCurrencyId As Integer = api.Members.GetMemberId(Dimtype.Consolidation.Id, "USD")
Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(fxRateTypeName, timeId, sourceCurrencyId, destCurrencyId)
Return averageRate

3. Create the Cube View with fist column as AvgRate and then next column as inverseavgrate. (Repaet for EOPRate)

A#AVGRATE

GetdataCell("1/A#AVGRATE"):Name("Inverse Average Rate")

                                    RESULT

  • WillVitale's avatar
    WillVitale
    Contributor II
    3 years ago

    Thanks so much! This is exactly what I was looking for.

    Will