XFgetcell pulls in future months as YTD negative

[RustyShacklefor]

How can I get xfgetcell formula to not pull in the next month as a negative? i am making a YTD file and currently have Jan and Feb number but march pulls in the YTD amount but negative and causes the ytd amount to be $0. 

[aricgresko]

We use UD8 as a Reporting Helpers dimension and have a Scaling hierarchy to scale the data in whole numbers, thousands, or millions.  If U8#None was selected, we would see the inverse number as you're describing.  Since the members in my screen shot below are DynamicCalc's, we do not see those inverse values.  My second screen shot shows how this works in a quick view.

[MarkHoughton]

Hi aricgresko,

What is the formula you use to dynamic calc the Thousands ? 

Regards

Mark

[aricgresko]

Here's the formula on the ScaledThousands UD8 member in my original screen shot

If viewMember.IsAnnotationType = False Then
If api.Members.HasChildren(objTimeDimPk, api.Pov.Time.MemberID) And api.Data.GetDataCell("U8#None").CellStatus.IsRealOrDerivedData = True And objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else

Dim currTime As String = api.Pov.Time.Name
Dim currTimeID As Integer = api.Pov.Time.MemberId
Dim currPeriod As Integer = api.Time.GetPeriodNumFromId(currTimeID)
Dim currYear As Integer = api.Time.GetYearFromId(currTimeID)
Dim globalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim globalTimeID As Integer = api.Time.GetIdFromName(globalTime)
Dim globalPeriod As Integer = api.Time.GetPeriodNumFromId(globalTimeID)
Dim globalYear As Integer = api.Time.GetYearFromId(globalTimeID)
If currYear = globalYear And currPeriod > globalPeriod Then
If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then

If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If

Else
Return Nothing
End If
End If
Else
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then

If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else 
Return Nothing
End If
End If
End If
Else
Return Nothing
End If

[MarkHoughton]

Hi aricgresko,

I think some declarations are missing as it's not compiling correctly. I have added these

Dim MemberId As Integer
Dim objViewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberId)
Dim objAccountType = api.Account.GetAccountType(MemberId)

But am not sure of the declaration necessary for 

objTimeDimPk

Can you see if there is any additional code to that which you posted above.

Regards

Mark

[aricgresko]

Sorry - I did miss a few rows of syntax.  

Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
Dim objaccountid As Integer = api.Pov.Account.MemberId
Dim objAccountType As AccountType = api.Account.GetAccountType(objaccountid)
Dim objTimeDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")

[aricgresko]

Here's the full code used to scale to thousands.  My original response missed the top 4 lines of code.

Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
Dim objaccountid As Integer = api.Pov.Account.MemberId
Dim objAccountType As AccountType = api.Account.GetAccountType(objaccountid)
Dim objTimeDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")


If viewMember.IsAnnotationType = False Then
If api.Members.HasChildren(objTimeDimPk, api.Pov.Time.MemberID) And api.Data.GetDataCell("U8#None").CellStatus.IsRealOrDerivedData = True And objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else

Dim currTime As String = api.Pov.Time.Name
Dim currTimeID As Integer = api.Pov.Time.MemberId
Dim currPeriod As Integer = api.Time.GetPeriodNumFromId(currTimeID)
Dim currYear As Integer = api.Time.GetYearFromId(currTimeID)
Dim globalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim globalTimeID As Integer = api.Time.GetIdFromName(globalTime)
Dim globalPeriod As Integer = api.Time.GetPeriodNumFromId(globalTimeID)
Dim globalYear As Integer = api.Time.GetYearFromId(globalTimeID)
If currYear = globalYear And currPeriod > globalPeriod Then
If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then

If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If

Else
Return Nothing
End If
End If
Else
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then

If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else 
Return Nothing
End If
End If
End If
Else
Return Nothing
End If

[MarkHoughton]

Hi,

Many thanks for that, though the first line should be Dim ObjViewMember

Dim ObjviewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)

Then it works perfectly.

Mark

[aricgresko]

Other than changing the variable name you declared (viewMember vs. ObjViewMember), what's the difference?  It accomplishes the same thing, just personal preference?

[MarkHoughton]

Hi,

Well it just gave me a compile error until I changed it, that's all. Otherwise, perfect. Appreciate your help in resolving this.

Mark

[aricgresko]

Interesting - I don't have any compile issues.  Either way, glad it's working for you!