help with a conditional statement
Hello,
I wrote a simple formula for Wip Rev that checks the amount in Earned Revenue (that is calculated too) and calculates the difference between its current value(POV) and prior year end value(...
Forum Discussion
it is difficult to advise a solution or a fix why this is not working when you add a conditional statement! totally surprising. I solved similar problem using DataBuffer. example a opening sales price account need to be copied from prior year closing sales price, here is how you can do
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#OpeningSalesPrice:O#Import")Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "A#ClosingSalesPrice:T#POVPrior1:O#BeforeAdj", destinationInfo)If Not sourceDataBuffer Is Nothing ThenDim resultDataBuffer As DataBuffer = New DataBuffer()For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.ValuesIf (Not sourceCell.CellStatus.IsNoData) ThenresultDataBuffer.SetCell(api.DbConnApp.SI, sourceCell)End IfEnd IfNextapi.Data.SetDataBuffer(resultDataBuffer, destinationInfo)End If
I figured out why the conditional statement is not working. When I add UD1=Top (In JTDRevenueLoaded) to be validated for the nothing or zero then it works, however we need to be able to differentiate this condition based on the UD1 members. So for each UD1 member it can be true or false...How can I do that?
If you need to be performing the test of JTDRevenueLoaded at each base UD1, then I would:
- Use an Eval2() in a single Calculate() statement, passing in current month JTDRevenueLoaded and prior month JTDRevenueLoaded as the two DataBuffers to evaluate.
- Within the OnEval handler, pass through the current month DataBuffer, and for <>0 cells, then lookup the corresponding cell in the other buffer and write the sum of the two cells together.
The above method eliminates the need for the GetDataCell(), as you will perform the check on a cell-by-cell level. If you need to perform the <>0 test at base level of UD1, but at summary of other dims, while retaining the detail of the other dims, then you may need an Eval3() so you can also pass-in the summary at which you wish to do the testing at (else summarize the data within the OnEval handler). There's a fair bit of detail I've left out which will vary depending on the specific details of your data and requirements, but it should point you in the correct direction.
If you intend to stick with something along the lines of what you originally coded:
- There is no need to perform the GetDataCell() until the confirmed you are in a relevant Scenario, Entity, etc.
- If the JTDRevenueLoaded=0 (or IsNoData, which you should probably be considering too), it doesn't appear to me that any Calculate() statement is required.
- Your OnEval handler, if truly required, will never get invoked as you do not have any Eval()'s within the Calculate() statements.
- You should probably include RemoveZeros() to avoid needlessly propagating zeros or processing NoData cells.
