Skip to main content
W
WillVitale
Contributor
May 26, 2023
Solved

Inverse Currency Rate

  • May 26, 2023
  • 1 reply
  • 0 views

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 work?

Thanks,

Will

Best answer by OS_Pizza

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

OS_Pizza_2-1685177636795.png

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

OS_Pizza_3-1685177967767.png

OS_Pizza_4-1685178033818.png

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

                                    RESULT

OS_Pizza_0-1685177467139.png

1 reply

O
OS_PizzaAnswer
Veteran
May 27, 2023

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

OS_Pizza_2-1685177636795.png

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

OS_Pizza_3-1685177967767.png

OS_Pizza_4-1685178033818.png

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

                                    RESULT

OS_Pizza_0-1685177467139.png

    W
    WillVitaleAuthor
    Contributor
    May 30, 2023

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

    Will

    Terms & ConditionsAccessibility statement