Forum Discussion

pranav46's avatar
pranav46
Contributor II
3 months ago

Allocation help when both side of buffer are unbalanced

Hey, I am working on rule to allocate cost to product. It worked when one side of buffer is unbalanced but I do have both. Here is the example. Not sure how can we do? My rate are by Product and NO...
Finance
  • TheJonG's avatar
    TheJonG
    3 months ago

    This concept is addressed in the 'Financial Rules and Calculations Handbook'. It requires a nested Data Buffer Cell Loop where both data buffers are looped through and the dimensions properties of both are applied to the result cell. Below is the example of the data and the corresponding rule:

     

     

    Here is the example given in the book:

    If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("DoubleUnbalanced") Then
						'----------------------------------------------------------------------------------------------------------------------
							'Book Reference:		Chapter 4 - DBCL Example - DBCL vs Eval
							'Purpose:				Example of a Data Buffer Cell Loop used to solve the Double Unbalanced problem
							'Run From:				Data Mgmt Step - Chapter 4 Examples - DoubleUnbalanced
							'View Results From:		Cube View - Chapter 4 Examples - Shipping Expense Calculation
							'Created by: 			Jon Golembiewski on 1/6/2022
							'------------------------------------------------------------------------------------------------------------------------	
														
							api.Data.ClearCalculatedData(True,True,True,True,"A#ShippingExpense")
							
							'Create a result data buffer and destination info to add the cells to later
							Dim resultDataBuffer As DataBuffer = New DataBuffer()
							Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("O#Import:I#None")
							
							'Declare the Volume data buffer with U2 fixed on None
							Dim volumeDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(A#Volume:O#Top:I#Top:U2#None)")
							
							'Retrieve the Sales Account Id before the loops so we don't retrieve it for each iteration
							Dim shippingExpenseId As Integer = api.Members.GetMemberId(dimtypeid.Account, "ShippingExpense")
							
							'Loop through the cells of the Volume data buffer
							For Each sourceCellVolume As DataBufferCell In volumeDataBuffer.DataBufferCells.Values
								'Retrieve the volume amount to be used in the calculation later
								Dim volumeAmount As Decimal = sourceCellVolume.CellAmount
								'Declare the Shipping Drivers data buffer with U1 fixed on None
								Dim shippingDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(A#ShippingDrivers:O#Top:I#Top:U1#None)")
								
									For Each sourceCellShipping As DataBufferCell In shippingDataBuffer.DataBufferCells.Values
										
										Dim shippingAmount As Decimal = sourceCellShipping.CellAmount
										
										'Create a new result cell to eventually add to the result data buffer
										'The cell properties of the source cell will be inherited
										Dim resultCell As New DataBufferCell(sourceCellShipping)
																		
										'Set the target members For the XFCommon members
										resultCell.DataBufferCellPk.AccountId = shippingExpenseId
										'Inherit the UD1 detail from the Volume Cell
										resultCell.DataBufferCellPk.UD1Id = sourceCellVolume.DataBufferCellPk.UD1Id
										'Inherit the UD2 detail from the Shipping Driver Cell
										resultCell.DataBufferCellPk.UD2Id = sourceCellShipping.DataBufferCellPk.UD2Id
										
										'Do the logic to calculate the result amount
										resultCell.CellAmount = volumeAmount * sourceCellShipping.CellAmount
										
										'Add the new cell to the Result Data Buffer
										resultdatabuffer.SetCell(si,resultCell)
									Next														
							Next
								
							'Save the Data Buffer using the Result Data Buffer and Destination Info
							api.Data.SetDataBuffer(resultdatabuffer,destinationinfo)

    Result:

     

Henning's avatar
Henning
Valued Contributor II

Hi, so you want to multiply the total product number by the allocation key on each product in %?

And then you want to allocate this number to the base members of COP, account and function?

If we take COP as an example, how many COPs are there that the 25% need to be allocated to? In your example, you allocate the full 25% (i.e. 12.5k) to COP US. I assume that there are more COPs to allocate to. In this case, the system needs to know the target COPs. How many are there, and what % out of the 25% should be allocated to each one? 

The same applies to accounts and functions. As in column B, you are using No COP, no account and no function, there is no relation the system can use to attribute the target value to a destination intersection (POV for each target cell). The easiest is to collect the allocation keys (% in column B) by COP, account and function. Then you just multiply the keys in % by Total Product. 

pranav46's avatar
pranav46
Contributor II
3 months ago

Hi Henning,

Yes, it applies to any COP which import product to US entity in above example and same case for Function & account because Revenue is in diff function. Since, I am new to OS from Hyperion world, did not know what the capability of US. Anyway, followed John example and able to write a code. 

Anyway thanks for responding!