Skip to main content
K
KK2012
Newcomer
August 14, 2024
Solved

U8 Member formula

  • August 14, 2024
  • 4 replies
  • 0 views

Hi, I am trying to achieve variance and change sign flip using UD8 member and based on account type however it is working for some accounts and not for all types of accounts. If someone handles the situation thru UD8 member, please share your experience or thoughts here. Thank you. 

Here is the quick code snipped what I am using. 

Dim acctType As String = api.Account.GetAccountType(api.Pov.Account.MemberId).Name
If api.cons.IsLocalCurrencyForEntity And acctType.XFEqualsIgnoreCase("Asset") Then
  api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1))")
Else If api.cons.IsLocalCurrencyForEntity And acctType.XFEqualsIgnoreCase("Liability") Then
  api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1)*(-1))")
End If 

Best answer by TheJonG

Hi - In a stored formula, the api.Pov functions will only work on Data Unit dimensions (Cube, Entity, Time, Scenario, Cons). So api.Pov.Account is not going to work. It will actually return your user POV for account (POV on the right side of the app) which is why its working for some accounts. Instead, you need to use a filter on your calculation which will filter the Data Buffer cells. You will no longer need an if statement but rather two api.Data.Calculate statements to handle the Assets and Liability account types. Note that you will need to replace 'A#AllAccounts' with your account parent name.

See below:

'Asset
api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1))","A#AllAccounts.Base.Where(AccountType = Asset)")

'Liability
 api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1)*(-1))","A#AllAccounts.Base.Where(AccountType = Liability)")

 

4 replies

T
OneStream Employee
TheJonGOneStream EmployeeAnswer
OneStream Employee
August 14, 2024

Hi - In a stored formula, the api.Pov functions will only work on Data Unit dimensions (Cube, Entity, Time, Scenario, Cons). So api.Pov.Account is not going to work. It will actually return your user POV for account (POV on the right side of the app) which is why its working for some accounts. Instead, you need to use a filter on your calculation which will filter the Data Buffer cells. You will no longer need an if statement but rather two api.Data.Calculate statements to handle the Assets and Liability account types. Note that you will need to replace 'A#AllAccounts' with your account parent name.

See below:

'Asset
api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1))","A#AllAccounts.Base.Where(AccountType = Asset)")

'Liability
 api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1)*(-1))","A#AllAccounts.Base.Where(AccountType = Liability)")

 

    K
    KK2012Author
    Newcomer
    August 15, 2024

    Thank you, It worked.

    Just FYI. Just additional step added, we have seen slight deltas in variance when it is applied to USD (reporting currency) however for Local currency it worked perfectly fine since wanted to apply this at local currency level. After remove, Remove Zeros from the member script of the code, it worked well for USD as well. Thank you again!

      D
      db_pdx
      Contributor
      August 14, 2024

      This is a DynamicCalc UD8, correct? I'd recommend the out-of-box BWDiff which automatically handles account types.

      Return api.Data.GetDataCell("BWDiff(U8#None, U8#None:T#PovPrior1)")

      Cheers,  -db

      K
      KK2012Author
      Newcomer
      August 15, 2024

      Thank you, I will test this one too. 

      K
      KK2012Author
      Newcomer
      August 16, 2024

      Hi All,

      One more observation, variance is working for all account types of members except Dynamic calc account type members ? Do we need to handle this separately? thank you!

      K
      KK2012Author
      Newcomer
      August 19, 2024

      Hi All,

      We have variance column working fine for all account types except for Dynamic account types when we use the below formula.

      Return api.Data.GetDataCell("BWDiff(U8#None, U8#None:T#PovPrior1)")

      Regards,

      Krishna

      Terms & ConditionsAccessibility statement