I'm developing an On Time and In Full calculation, OTIF Dashboard. Previously i used backend script to develop the calculation but my customer would like to have some kind of what-if analysis therefore i have to do the calculation "on the fly" in frontend expression.
Scenario at below:
OTIF by Sales Order Line: When fulfilled On time + In full, then OTIF is fulfilled.
| Sales Order | Sales Order Line | On Time | In Full | OTIF |
| | Formula | =count({<SO_Key={"=OTIF_Date >= Cust_GI_Date"}>}SO_Key) | count({<SO_Key={"=sum(Deliver_Net_Weight_KG)>=MinOrderQuanity
and sum(Deliver_Net_Weight_KG)<=MaxOrderQuantity"}>}
SO_Key) | count({<SO_Key={"=sum(Deliver_Net_Weight_KG)>=MinOrderQuanity
and sum(Deliver_Net_Weight_KG)<=MaxOrderQuantity"}>*< SO_Key={"=OTIF_Date >= Cust_GI_Date"}>}
SO_Key) |
| | Total | 3 | 4 | 3 |
| 20012322 | 10 | 1 | 1 | 1 |
| 20012322 | 20 | 1 | 1 | 1 |
| 20012322 | 30 | 1 | 1 | 1 |
| 20012322 | 40 | 0 | 1 | 0 |
The above calculation i have no issue but when come to calculate by Sales Order Line.
How to calculate Sales Order? Is when any of the Sales Order Line's OTIF is 0, the entire Sales Order's OTIF will be 0. How to get the below result using frontend expression? I know this message might trouble but I seeking your advice. Thanks
| Sales Order | On Time | In Full | OTIF |
| Formula | The on time is zero because one of the Sales order line is zero | The in full is 1 because all the Sales Order Line is met. | Hence, 0. |
| Total | 0 | 1 | 0 |
| 20012322 | 0 | 1 | 0 |
