Forum Discussion

Ioana's avatar
Ioana
New Contributor III
2 years ago

Redirect Data Buffer result

Hello,

I am trying to adjust the code below to handle the following situation:

The client loaded data for Test Rate and Test Count on UD1 "ID".

The data buffers defined for prior month (TotDirCOGSDataBuffer and DirCOGSDataBuffer) do not have any values for UD1 "ID". The client wants to do the calculation for UD1 "ID" as follows: take the prior month data from another UD1 member, multiply with the Test Rate and Test Count for UD1 "ID" and place the results on UD1 "ID".

How I can accommodate this change, considering this will happen also in the future when they need to perform a calculation on a UD1 member which does not have values in prior months?

'api.Data.ClearCalculatedData(True,True,True,True,"U7#DirCOGS_Calc")	
													api.Data.ClearCalculatedData(True,True,True,True,,,,,,,,,,,"U7#DirCOGS_Calc",)
													
													'Create a result data buffer and destination info to add the cells to later
															Dim resultDataBuffer6 As DataBuffer = New DataBuffer()
															Dim destinationInfo6 As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("F#EndBalLoad:O#Import:I#None" & _
				                                                                                          																 ":U5#None:U6#None:U7#DirCOGS_Calc:U8#None")																																 																																											 
															'Get total Direct COGS to use in Calc
'															Dim TotDirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(A#Dir_COGS:F#Top:O#Top:T#POVPrior1:I#Top" & _ 
'																																												":U5#None:U6#None:U7#Top:U8#Top),U2#101)")
															Dim TotDirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(A#Dir_COGS:F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																																												":U5#None:U6#None:U7#Top:U8#Top,U2#101))")
															For Each TotDirCOGSSourceCell As DataBufferCell In TotDirCOGSDataBuffer.DataBufferCells.Values
																Dim UD1 As String = TotDirCOGSSourceCell.GetUD1Name(api).ToString 'Brand	
																Dim TotDirCOGSAmount As Decimal = TotDirCOGSSourceCell.CellAmount
																	
																'Declare source data buffers for Run Rate
'																Dim DirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#POVPrior1:I#Top" & _ 
'																																													":U5#None:U6#None:U7#Top:U8#Top),A#Dir_COGS.Base,U1#" & UD1 & ",U2#101)")																													
																Dim DirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																																													":U5#None:U6#None:U7#Top:U8#Top,A#Dir_COGS.Base,U1#" & UD1 & ",U2#101))")																														
																For Each DirCOGSSourceCell As DataBufferCell In DirCOGSDataBuffer.DataBufferCells.Values
'																	Dim UD2 As String  = DirCOGSSourceCell.GetUD2Name(api).ToString ' Department
																	Dim DirCOGSAmount As Decimal = DirCOGSSourceCell.CellAmount
																							
																	Dim resultCell As New DataBufferCell(DirCOGSSourceCell)
																	
																	'Declare variables for TestCount
																	Dim TestCountCurr As Decimal = api.data.getdatacell("A#TestCount:T#" & POVPERIOD & ":V#Periodic:F#Top:O#Top:I#Top:U1#" & UD1 & ":U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#Top:U8#Top").CellAmount
																	
																	'Declare variables Test Rate
																	Dim TestRate As Decimal = api.data.getdatacell("A#TestRate:T#" & sPOVYEAR & Quarter &":V#Periodic:F#EndBalLoad:O#Forms:I#None:U1#" & UD1 & ":U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None").CellAmount												
																	
'																	If UD2 = "101" Then		
																		resultCell.CellAmount =  DirCOGSAmount/TotDirCOGSAmount * TestCountCurr * TestRate
'																	End If
																	
																	resultDataBuffer6.SetCell(si, resultCell)

																Next
															Next
																		
																api.Data.SetDataBuffer(resultDataBuffer6,destinationInfo6)

 

Finance

4 Replies

Sort By
  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    For dealing with data that may not exist where you need to divide, you can add ternary check for 0 and set the value to 1 instead.

    TestRate = If(TestRate<>0, TestRate, 1)

     

    • Ioana's avatar
      Ioana
      New Contributor III
      2 years ago

      Hi Robb,

      The data buffers should look at prior period. The Test factors should look at current period, as how the code shows.

      Data buffers do not have values on UD1 ID on prior period.

      Test factors have data on UD1 ID on current period.

      The calculation logic should be = DirCOGSDataBuffer / TotDirCOGSDataBuffer * Test Rate * Test Count, where

      DirCOGSDataBuffer and TotDirCOGSDataBuffer are values from prior period on UD1 ID

      Test Count and Test Rates are values from current period on UD1 ID

      But the problem is DirCOGSDataBuffer and TotDirCOGSDataBuffer do not have values on UD1 ID in prior period. Because of that, the client wants to use another UD1 member, let's say UD1 A, and replace the calculation as follows: DirCOGSDataBuffer UD1 A / TotDirCOGSDataBuffer UD1 A * Test Rate UD1 ID * Test Count UD1 ID.

      The result should be placed on UD1 ID.

      This should happen only for this special cases when there is no data on certain UD1's for prior period. The rest of the cases should follow the logic as it is now in the code.

       

       

      • JackLacava's avatar
        JackLacava
        Community Manager
        2 years ago

        When faced with complexity in programming, the answer is to break the logic down into smaller, more manageable chunks.

        1. Create a sub containing only the calculating activity, accepting parameters for the elements that are going to change.
        2. Create a function that will return True/False depending on whether there is data in previous periods. Again you will need some parameter, it might well be a buffer.
        3. Have a simple If/Then/Else switch that, depending on the results of 2, will run 1 with some parameter or other.

        Do this first, check the results are correct, then worry about ways to speed things up later.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    Add a test for 0 in testCountCurr.  if 0, use brand A:

    	api.Data.ClearCalculatedData(True,True,True,True,,,,,,,,,,,"U7#DirCOGS_Calc",)
	
	'Create a result data buffer and destination info to add the cells to later
	Dim resultDataBuffer6 As DataBuffer = New DataBuffer()
	Dim destinationInfo6 As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("F#EndBalLoad:O#Import:I#None" & _
	                                                 																 ":U5#None:U6#None:U7#DirCOGS_Calc:U8#None")																																 																																											 
	'Get total Direct COGS to use in Calc
	Dim TotDirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(A#Dir_COGS:F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																														":U5#None:U6#None:U7#Top:U8#Top),U2#101)")
	Dim TotDirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(A#Dir_COGS:F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																														":U5#None:U6#None:U7#Top:U8#Top,U2#101))")
	For Each TotDirCOGSSourceCell As DataBufferCell In TotDirCOGSDataBuffer.DataBufferCells.Values
		Dim UD1 As String = TotDirCOGSSourceCell.GetUD1Name(api).ToString 'Brand	
		Dim UD1A As String = $"{UD1}A"   'when brand is 0 use brand A
		Dim TotDirCOGSAmount As Decimal = TotDirCOGSSourceCell.CellAmount
			
		'Declare source data buffers for Run Rate
		Dim DirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																															":U5#None:U6#None:U7#Top:U8#Top),A#Dir_COGS.Base,U1#" & UD1 & ",U2#101)")																													
		Dim DirCOGSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(F#Top:O#Top:T#POVPrior1:I#Top" & _ 
																															":U5#None:U6#None:U7#Top:U8#Top,A#Dir_COGS.Base,U1#" & UD1 & ",U2#101))")																														
		For Each DirCOGSSourceCell As DataBufferCell In DirCOGSDataBuffer.DataBufferCells.Values
			Dim UD2 As String  = DirCOGSSourceCell.GetUD2Name(api).ToString ' Department
			Dim DirCOGSAmount As Decimal = DirCOGSSourceCell.CellAmount
									
			Dim resultCell As New DataBufferCell(DirCOGSSourceCell)
			
			'Declare variables for TestCount
			Dim TestCountCurr As Decimal = api.data.getdatacell("A#TestCount:T#" & POVPERIOD & ":V#Periodic:F#Top:O#Top:I#Top:U1#" & UD1 & ":U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#Top:U8#Top").CellAmount
			If TestCountCurr = 0 Then 'use brand A
				TestCountCurr = api.data.getdatacell("A#TestCount:T#" & POVPERIOD & ":V#Periodic:F#Top:O#Top:I#Top:U1#" & UD1A & ":U2#Top:U3#Top:U4#Top:U5#None:U6#None:U7#Top:U8#Top").CellAmount
			End If
			
			'Declare variables Test Rate
			Dim TestRate As Decimal = api.data.getdatacell("A#TestRate:T#" & sPOVYEAR & Quarter &":V#Periodic:F#EndBalLoad:O#Forms:I#None:U1#" & UD1 & ":U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None").CellAmount												
			
			If UD2 = "101" Then		
				resultCell.CellAmount =  DirCOGSAmount/TotDirCOGSAmount * TestCountCurr * TestRate
			End If
			
			resultDataBuffer6.SetCell(si, resultCell)
	
		Next
	Next
			
	api.Data.SetDataBuffer(resultDataBuffer6,destinationInfo6)

    ps, its always a good idea to test denominators for 0 before using them.