Forum Discussion

OlaWidera's avatar
OlaWidera
New Contributor II
3 days ago

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(POVPriorYearM12) - this part worked, but then I added a conditional statement saying that if Earned Revenue is zero then Wip Rev should be zero too else run the math (POV - POVPriorYearM12) and only first part works, but math doesn't.  I tried a similar statement for the dynamic calc and I was successful, but here no. Please let me know if you can look at the statement and suggests what is wrong...

thank you

 

Admin

7 Replies

  • skm's avatar
    skm
    New Contributor II
    3 days ago

    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 Then
    Dim resultDataBuffer As DataBuffer = New DataBuffer()
    For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values
    If (Not sourceCell.CellStatus.IsNoData) Then
    resultDataBuffer.SetCell(api.DbConnApp.SI, sourceCell)
    End If
     
    End If
    Next
    api.Data.SetDataBuffer(resultDataBuffer, destinationInfo)
    End If
    • OlaWidera's avatar
      OlaWidera
      New Contributor II
      2 days ago

      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?

      • rhankey's avatar
        rhankey
        Contributor III
        2 days ago

        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.
  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    3 days ago

    I would include a check for NoData in your if statement:

    Dim JTDRevenueLoadedDataCell = api.Data.GetDataCell("A#REVENUE_EARNED:U8#None:O#Top:F#EndBal:V#YTD:T#POV") as DataCell
Dim JTDRevenueLoadedDataCellStatus = JTDRevenueLoadedDataCell.CellStatus as DataCellStatus
Dim JTDRevenueLoaded = JTDRevenueLoadedDataCell.CellAmount as Decimal

If api.Pov.Cube.Name.XFEqualsIgnoreCase("WIP_Cube") Then 'run for the WIP Cube

    If ((Not api.Entity.HasChildren()) OrElse api.Cons.IsForeignCurrencyForEntity()) Then 'Run for base entities and all Canadian Entities

        If (JTDRevenueLoadedDataCellStatus.IsNoData OrElse JTDRevenueLoaded = 0) Then 'if Earned Revenue is zero

     

    • OlaWidera's avatar
      OlaWidera
      New Contributor II
      2 days ago

      Hi Rob,

      Thank you for this suggestion. I implemented it but still didn't get the positive outcome. I technically should have data (zero) because I would enter data into a test, but to your point in real life we might have no data situation as well so will keep that part of the formula

  • skm's avatar
    skm
    New Contributor II
    3 days ago

    I would first attempt to copy what is A#EARNED_REVENUE to the A#REVENUE_WIP2 account to make sure populating value into A#REVENUE_WIP2 is working without any condition. The reason for this is because I want you to make sure the source A#EARNED_REVENUE has a value to copy in the first place, as you said this is also calculated account. 

    Regarding dynamic calc on A#REVENUE_WIP2 may work as that is executed only when used in the presentation layer.

    I am advising you to set the correct formula pass number such A#EARNED_REVENUE runs in a pass earlier than A#REVENUE_WIP2.

    On other note, you don't need OnEvalDataBuffer as you are not using any filtering logic on the sourcedata buffer.

    • OlaWidera's avatar
      OlaWidera
      New Contributor II
      3 days ago

      Thank you for looking at this. If I remove the conditional statement then formula works, but the reason it is there in the first place is so we don't have a negative amount in Revenue_WIP , because it means that given job closed and negative revenue doesn't represent any value.

      Also, I keep this test in the development and make sure that Revenue Earned has the data or not to see results based on the conditional statement.

      Formula passes seem fine to:. Rev Earned is pass 2 and Rev Wip pass 4. 

      Does it matter that Revenue_Earned is a balance account and Revenue WIP is set as revenue? I am not sure what else to look at.

      Thank you again for looking at this and to your point removed OnEvalDataBuffer as you suggested - I copied it from the Golf app.

      thank you,

      Ola