Count on if condition

[bnelson111]

Good afternoon, i have the following chart created to measure on time deliveries.  

The calculation for the OTIF is as follows.

=if(ActualShipDate >ShipDate and PickedQty<(ShipQty/100*$(tol)),'Epic Fail',
if(ActualShipDate >ShipDate,'Late Delivery',

if(PickedQty<(ShipQty/100*$(tol)),'Short Shipped',
'Nice')))

What i would like to do is have a summary text box with expression to count how many 'Nice' which is (ActualShipDate <ShipDate and PickedQty<(ShipQty/100*$(tol)). 

Then divide it by Total count to have a percentage of Nice.

Hope this makes some sense. Thank you in advance.

[sunny_talwar]

May be this

=Count(
If(ActualShipDate > ShipDate and PickedQty<(ShipQty/100*$(tol)), 0, 
If(ActualShipDate >ShipDate, 0,
If(PickedQty < (ShipQty/100*$(tol)), 0, ShipDate)))
)
/
Count(ShipDate)

[bnelson111]

Not working unfortunately should be getting 0.4. Seems to be counting all 5 still

[sunny_talwar · Accepted solution]

My bad, try this

=Count(
If(ActualShipDate > ShipDate and PickedQty<(ShipQty/100*$(tol)), Null(), 
If(ActualShipDate >ShipDate, Null(),
If(PickedQty < (ShipQty/100*$(tol)), Null(), ShipDate)))
)
/
Count(ShipDate)

[bnelson111]

I also have a unique identifier TableRecId

[bnelson111]

Thank you, very happy just changed the Ship date to TableRecId.

[sunny_talwar]

In that case, try this

=Count(DISTINCT {<TableRecId = {"=ActualShipDate <= ShipDate and PickedQty >= (ShipQty/100*$(tol))"}>} TableRecId)
/
Count(DISTINCT TableRecId)

[bnelson111]

Should i maybe have this in load statement to keep it very clean.

[sunny_talwar]

You can def. look to perform this part of the calculation in the script

ActualShipDate <= ShipDate and PickedQty >= (ShipQty/100*$(tol)) as Flag

Or you can break it into two flags

ActualShipDate <= ShipDate as Flag1,
PickedQty >= (ShipQty/100*$(tol)) as Flag2

and then use this as set analysis.

[bnelson111]

BAck on now, will have a look, thank you