The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Assessed Loss utilised in current year - Tax
Hi, anyone has worked with a similar member script or rule that has a conditions - eg assessed loss utilised in the current year is based on higher of $1 million and 80% of Current Taxable Income acc...
TheJonG
OneStream Employee
OneStream EmployeeHi - this is possible but in order to analyze cell amounts in a calculation you will need to declare a data buffer and then loop through the cells.
Hi, thanks for the reply. Could you show me an example?
- TheJonG
Here is an example of a Data Buffer Cell loop that would be a good starting point for your situation. I interpreted your explanation as you need to calculate the Assessed Loss Utilised Account which is set in the Destination Info on line 5. To determine this amount, you need to look at the Current Taxable Income account and take the greater of 80% of that amount or 1,000,000. So we declare a data buffer for that account (line 8 ) and then loop through all the cells within it. For each cell, we look at the cell amount and do the logic (lines 21-26). We then add the cell to the result buffer (empty buffer we created on line 4) and then write that result buffer to the cube on line 33. The DestinationInfo has the Assessed Loss Utilised account defined we ultimately write back to that account. Not sure if I got that all correct but this should give you a framework to start with.
api.Data.ClearCalculatedData(True,True,True,True,"A#AssessedLossUtilised") '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("A#AssessedLossUtilised:O#Import:I#None") 'Declare starting data buffer to loop through Dim CurTaxableIncDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(A#CurrentTaxableIncome:O#Top:I#Top)") 'Loop through the cells of the data buffer For Each sourceCell As DataBufferCell In CurTaxableIncDataBuffer.DataBufferCells.Values '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(sourceCell) 'Set the result cell to the destination account resultcell.DataBufferCellPk.OriginId = DimConstants.Import resultCell.DataBufferCellPk.ICId = DimConstants.None 'Do the logic to calculate the result amount If (resultCell.CellAmount * .8) > 1,000,000 Then resultCell.CellAmount = resultCell.CellAmount * .8 Else resultCell.CellAmount = 1,000,000 End If resultDataBuffer.SetCell(si,resultCell) Next 'Save the Data Buffer using the Result Data Buffer and Destination Info api.Data.SetDataBuffer(resultDataBuffer,destinationInfo)
