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)"
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:
Also, you will need to set
individual
property totrue
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.