Calculation expression

[ba11]

Hi All,

I'm trying to create an expression in my app, I have a field that is 'WORK_ORDER' with SUB_IDs ranging from 0 upwards. Other fields I want to use in the calculation include Total Quantity, WORK_ORDER.DIMENSION,  WORK_ORDER.BLANKS and WORK_ORDER.CAVITY.

I want to calculate the Total Quantity * (([WORK_ORDER.DIMENSION] * [WORK_ORDER.BLANKS])/[WORK_ORDER.CAVITY])).

The problem I'm having is I want to only use the Total Quantity where the SUB_ID = 0 for this calculation.

So if SUB_ID = 0, I want Total Quantity * (([WORK_ORDER.DIMENSION] * [WORK_ORDER.BLANKS])/[WORK_ORDER.CAVITY])) and if SUB_ID>0, I still want the same result as above but utilize the Total Quantity where SUB_ID=0 for both parts of the calculation and I'm having a hard time with that. It either only returns values where SUB_ID=0 or uses Total Quantity where SUB_ID>0 for the other calculations. 

Not sure it's clear how I've explained it, but I'll appreciate any help I can get.

Thank you.

[Anil_Babu_Samineni]

@ba11 Perhaps this?

if(SUB_ID = 0, Sum(Total Quantity) * (([WORK_ORDER.DIMENSION] * [WORK_ORDER.BLANKS])/[WORK_ORDER.CAVITY])), if(SUB_ID>0, Sum({<SUB_ID={0}>}Total Quantity) * (([WORK_ORDER.DIMENSION] * [WORK_ORDER.BLANKS])/[WORK_ORDER.CAVITY]))))

[ba11 · Accepted solution]

Thank you, this didn't work but instead I added both fields that make up the total quantity in the load script so I didn't have to do the calculation by SUB_ID in my app.

Thanks again.