On Time and In Full in expression

kenji_khoo — Wed, 15 Jul 2020 16:07:10 GMT

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

Re: On Time and In Full in expression

Kushal_Chawda — Wed, 15 Jul 2020 16:17:00 GMT

can you try below. If not working then would you be able to provide sample?

Count(total <[Sales order line]>{<SO_Key={"=sum(Deliver_Net_Weight_KG)>=MinOrderQuanity
and sum(Deliver_Net_Weight_KG)<=MaxOrderQuantity"}>*< SO_Key={"=OTIF_Date >= Cust_GI_Date"}>}
SO_Key)

topic Re: On Time and In Full in expression in QlikView

On Time and In Full in expression

Re: On Time and In Full in expression