Forum Discussion

RustyShacklefor's avatar
RustyShacklefor
New Contributor III
3 years ago

XFgetcell pulls in future months as YTD negative

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...
Excel Add In
aricgresko's avatar
aricgresko
Contributor III
3 years ago

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's avatar
    aricgresko
    Contributor III
    3 years ago
    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's avatar
      MarkHoughton
      Contributor
      3 years ago

      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's avatar
        aricgresko
        Contributor III
        3 years ago

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