webdatarocks

How to add conditional fields

I am trying to create a report where I have to sum fields by specific type and then subtract from another.

ex. Net Operating Income = Total Income - Total Expense

I would have data that has a type field ("INCOME", "EXPENSE").

I created something like this and it doesn't seem to be working.

"formula": "if(('type') == 'INCOME', ('amount'), 0) - if(('type') == 'EXPENSE', ('amount'), 0)"

Solution

  • WebDataRocks does not support strings comparison in the calculated values. Instead, you can replace "INCOME" and "EXPENSE" with some numeric values (e.g., 1 and 0). In this case, the formula would look as below:

    'if( sum("type") == 1, sum("amount") , 0)  - if( sum("type") == 0, sum("amount"), 0)'

    Also, you will need to set individual property to true to support both flat and pivot forms. Here is a CodePen for the demonstration: https://codepen.io/webdatarocks/pen/MWpvgZg.

    However, it may be easier for you to simply specify negative values for expenses instead of creating calculated value based on some additional field.