Forum Discussion
Solved
U8 Member formula
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 s...
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)")
TheJonG
OneStream Employee
OneStream EmployeeHi - 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)")
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!
