USD Override is aggregating with translated balance

[DCarrillo]

I created a USD Override form, however the translated value and the override value are both getting to the Top member. I was expected the override value to be getting to Top. 

Here is the formula for USD Override Input in the Flow dimension:

And the difference in POV between the translated and override for account 399500:

Here is the formula for A#399500/settings:

[Henning]

Hi, the first thing you might want to do is to set the aggregation weight of your override member to 0, this way this does not roll up into the top member (assuming a standard setup):

And you might want to clear the translated data on your closing member when you execute the override translation for USD. There are different ways, but I guess in your case this seems easiest. E.g.

Api.Data.ClearCalculatedData("F#EndBal_Input", False, True, False)

And I do not follow what you describe around A#399500. I hope the above helps, otherwise I would need some more information.

[T_Kress]

To add to what Henning said, your F#USD_Override_Input member normally has an agg weight of 0.  The only formula (aka FormulaPass1 or something) on this member is typically just to carry the input override forward from period to period until there is a change as shown in the member formula below:

If (Not api.Entity.HasChildren()) AndAlso api.Cons.IsLocalCurrencyforEntity() Then	

	Dim firstPeriod As Boolean = api.Time.IsFirstPeriodInYear(api.Pov.Time.MemberId)	'First period in Year
	
	If firstPeriod Then
		api.Data.Calculate("F#USDOverrideEndBal:O#Import = RemoveNoData(F#USDOverrideEndBal:T#POVPrior1:O#BeforeAdj)","A#Root.Base.Where((Text8 = 'Override') And (Text2 = ''))")
	Else
		api.Data.Calculate("F#USDOverrideEndBal:O#Import = RemoveNoData(F#USDOverrideEndBal:T#POVPrior1:O#BeforeAdj)","A#Root.Base.Where(Text8 = 'Override')")
	End If
End If

Then typically it is on your F#EndBal_Input member where you put the formula (FormulaPass2 or higher) that will look at the override and if there is one, apply that in translation rather than the standard translated amount.  Here is a formula example from the BluePrint app that goes on your F#EndBal_Input member:

If (api.Cons.IsForeignCurrencyForEntity()) Then	'If foreign currency
	
	If (Not api.Entity.HasChildren()) Then	'If base Entity
		api.data.Calculate("F#EndBalLoad = Eval(F#EndBalLoad:C#Local)", AddressOf OnEvalDataBuffer)	'Override the translated value	

	Else	'Else If parent Entity
		Dim mainEntitiesDimPk As DimPk = api.Dimensions.GetDim("MainEntities").DimPk	'Entity DimPk
		Me.OverrideCons(si, globals, api, args, mainEntitiesDimPk)	'Consolidate the override accounts to accomodate Auto Translation Currencies
	End If
End If


'End XFFormula
            Catch ex As Exception
                Throw New XFException(si, ex)
            End Try
        End Sub

'XFHelperFunctions

Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

	Try
		'Start with an empty list of result cells.
		eventArgs.DataBufferResult.DataBufferCells.Clear()

		Dim dataUnitPk As DataUnitPk = api.Pov.GetDataUnitPk()	'Get the current Data Unit Pk			
		Dim viewId As Integer = ViewMember.YTD.Id	'Get the YTD View member ID
		Dim endBalMemberId = api.Members.GetMemberId(DimType.Flow.Id, "EndBalLoad")	'EndBal Member Id
		Dim overrideMemberId = api.Members.GetMemberId(DimType.Flow.Id, api.Pov.Cons.Name & "OverrideEndBal")	'Override Member Id ("CurrentConsMember"OverrideEndBal)
		Dim resultOrigin As String
		
		For Each lcCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values	'Loop over the source cells.
			If (Not lcCell.CellStatus.IsNoData) Then	'Only include cells that have data
				If api.Account.Text(lcCell.DataBufferCellPk.AccountId,8).XFEqualsIgnoreCase("Override") Then 'Only allow this logic to happen for accounts that are tagged as "Overide" in text 8 on account 
					
						'Read the initial DataCell for C#Local:O#Top:F#EndBalLoad
						Dim tempDataCellPk1 As New DataCellPk(dataUnitPk, viewId, lcCell.DataBufferCellPk)
						tempDataCellPk1.ConsId = DimConstants.Local
						tempDataCellPk1.OriginId = DimConstants.Top
						tempDataCellPk1.FlowId = endBalMemberId
						Dim lcTopCell As DataCell = api.Data.GetDataCell(tempDataCellPk1)
						
						'Read the initial DataCell for C#Local:O#Top:F#"CurrentConsMember"OverrideEndBal
						Dim tempDataCellPk As New DataCellPk(dataUnitPk, viewId, lcCell.DataBufferCellPk)
						tempDataCellPk.ConsId = DimConstants.Local
						tempDataCellPk.OriginId = DimConstants.Top
						tempDataCellPk.FlowId = overrideMemberId
						Dim overrideTopCell As DataCell = api.Data.GetDataCell(tempDataCellPk)
						
						If lcTopCell.CellAmount <> 0.00 And overrideTopCell.CellStatus.IsRealData Then	'If the cell has a local currency value and the override has data
							
							'Get the override rate by dividing the total override by the total local currency
							Dim overrideRate As Decimal
							If overrideTopCell.CellAmount = 0.00 Then
								overrideRate = 0.00
							Else
								overrideRate = (overrideTopCell.CellAmount / lcTopCell.CellAmount)
							End If
							
							'If we are translating a AdjInput cell then the translated value should go to AdjConsolidated
							If lcCell.DataBufferCellPk.GetOriginName(api).XFEqualsIgnoreCase("AdjInput") Then
								resultOrigin = "AdjConsolidated"
							Else	'Write to the same origin
								resultOrigin = lcCell.DataBufferCellPk.GetOriginName(api)
							End If
						
							'Multiply the local currency value by the derived override rate to allocate across origins
							Dim resultCell As New DataBufferCell(lcCell)
							resultCell.DataBufferCellPk.OriginId = api.Members.GetMemberId(DimType.Origin.Id, resultOrigin)
							resultCell.CellAmount = overrideRate * lcCell.CellAmount
							eventArgs.DataBufferResult.SetCell(api.SI, resultCell, True)
							
						End If	
						
				End If
			End If
		Next

	Catch ex As Exception
		Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI, ex))
	End Try
End Sub


Private Sub OverrideCons(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs, ByVal mainEntitiesDimPk As DimPk)
	Try	
			
		Dim resultDataBuffer As New DataBuffer()
		Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
								
		'Get the Children in a list
		Dim objList As List(Of Member) = api.Members.GetChildren(mainEntitiesDimPk, api.Pov.Entity.MemberId)
		
		'Loop through the children
		For Each entity As Member In objList
			
			'Get a data buffer of all override account values in the currency we are translating to
			Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(E#" & entity.Name & ":F#EndBalLoad, [A#BalSheetAccts.Base.Where(Text8 = 'Override')])",,False)

			If sourceDataBuffer.DataBufferCells.Count > 0 Then	'If the data buffer is not empty
				For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values	'Loop over the cells
					If (Not sourceCell.CellStatus.IsNoData) Then	'if the source cell is not zero and is not no data
						
						'Add the cell to the data buffer
						resultDataBuffer.SetCell(si, sourceCell, True)
						
					End If
				Next
			End If
		Next
							
		'Set the data buffer									
		api.Data.SetDataBuffer(resultDataBuffer, destinationInfo)
	
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(api.SI, New XFException(api.SI, ex))
	End Try
End Sub

Lastly, normally there is not a formula on your account member.  The text field on the account will drive whether this rule in the Flow dim is applied. 

I hope this helps.  Again, the Blueprint app available for download on the Solution Exchange has a good example of flow member formulas.