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