Skip to main content
R
RustyShacklefor
Newcomer
March 9, 2022

XFgetcell pulls in future months as YTD negative

  • March 9, 2022
  • 4 replies
  • 0 views

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. 

    4 replies

    aricgresko
    aricgresko
    Veteran
    March 9, 2022

    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.

    aricgresko_1-1646855170799.png

    aricgresko_2-1646855228347.png

     

     

     

      M
      MarkHoughton
      Contributor
      April 19, 2022

      Hi aricgresko,

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

      Regards

      Mark

      aricgresko
      aricgresko
      Veteran
      April 19, 2022

      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
      M
      MarkHoughton
      Contributor
      April 20, 2022

      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
      aricgresko
      Veteran
      April 20, 2022

      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
      aricgresko
      Veteran
      April 20, 2022
      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

       

        M
        MarkHoughton
        Contributor
        April 20, 2022

        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

        T
        TyeshaAdams
        Newcomer
        March 20, 2025

        This worked great!  Thank you!

          Terms & ConditionsAccessibility statement