Inverse Currency Rate

WillVitale
New Contributor III

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

1 ACCEPTED SOLUTION

OS_Pizza
Contributor III

@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

View solution in original post

2 REPLIES 2

OS_Pizza
Contributor III

@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

WillVitale
New Contributor III

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

Will