I have made somecalculated fields in some charts, which I would like to use throughout my document, so I would like to add them add them to my script.
Based on the two fields "SALESPRICE" and "SALESQTY" I was able to calculate and add the field "Sales Amount" to my script, but when I try to add "Total Discount" and "Net Sales Amount" (Which are shown as disabled below) my script fails - I get the error message "invalid expression"
Is it not possible to use the IF and SUM function the way I try or have I just made a rookie mistake :-)
The problem is not sum and if in one expression. The problem is that if you use an aggregation function like sum, then all fields not used in aggregation functions need to be listed in a group by clause. But you can't use a group by with a preceding load. So you need to do the summing in the sql statement or use a resident load.
There's also a comma after ITEMID instead of a semicolon.
Edit: as Henric pointed out below I'm wrong about using a group by with a preceding load. It is possible. That doesn't mean it's a good idea though.