U8 Member formula

[KK2012]

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 

[TheJonG · Accepted solution]

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)")

[KK2012]

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!

[db_pdx]

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

[KK2012]

Thank you, I will test this one too. 

[KK2012]

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!

[KK2012]

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