03-12-2024 06:22 AM - edited 03-12-2024 06:24 AM
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)
03-12-2024 09:23 AM - edited 03-12-2024 09:24 AM
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)
03-12-2024 09:36 AM
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.
03-13-2024 06:36 AM
When faced with complexity in programming, the answer is to break the logic down into smaller, more manageable chunks.
Do this first, check the results are correct, then worry about ways to speed things up later.
03-12-2024 09:49 AM - edited 03-12-2024 09:53 AM
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.