Forum Discussion

VENKAB05's avatar
VENKAB05
New Contributor III
2 years ago

Override USD data with Custom Calculation.

Hello All,

Could be a very simple question but need help.

I have a calculation F#Flowmember= T#TimeMember1+T#TimeMember2 +T#TimeMember3 in Local which works fine.
I want a similar calc in USD as well, so i run the same custom calc through DM Step just changing the Consol Member to USD.

Now the problem is, when i run consolidation. the USD Calculated values are overwritten by translation numbers which i dont want to and retain the custom cal which i did in USD. 

Any idea on this one..THanks in advance..

Finance

1 Reply

  • Krishna's avatar
    Krishna
    Valued Contributor
    2 years ago

    VENKAB05  -  You have to set the following 

    1. for USD Accounts you should set the below property to true

    2. In the Flow you have to Create a Calc & Input Member. or You can also create a Finance Rule. 

    3. The below is the sample for the USD override formula but you need to change based on your requirement and also test this in Dev environment.

     

    If Not api.Entity.HasChildren Then
	
Dim destAcct As String = "A#APIC_Interco"
Dim destDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#APIC_Interco:F#EndingBal:C#USD)")

'USD ACCOUNT
Dim srcAcct As String = "A#USDAPIC_Interco"
Dim srcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#USDAPIC_Interco:F#EndBalInput:A#USDAPIC_Interco)")

If Not srcDataBuffer Is Nothing Then
	
	For Each srccell As DataBufferCell In srcDataBuffer.DataBufferCells.Values
		If srccell.CellAmount <> 0 Then
			Dim ud1Member As String = srccell.GetUD1Name(api)
			Dim ud3Member As String = srccell.GetUD3Name(api)
			Dim icp As String = srccell.GetICName(api)
			
			If Not destDataBuffer Is Nothing Then
				For Each sourcecell As DataBufferCell In destDataBuffer.DataBufferCells.Values
					If Not sourcecell.CellStatus.IsNoData Then
						api.Data.ClearCalculatedData(True,True,True,destAcct,,,"I#" & icp,"U1#" & ud1Member,,"U3#" & ud3Member,,,,,)

					End If 
				Next
			End If
		End If
	Next
	api.Data.Calculate("A#APIC_Interco:F#EndBalInput= RemoveZeros(A#USDAPIC_Interco:F#EndBalInput)")
End If
End If