
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count on if condition
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not working unfortunately should be getting 0.4. Seems to be counting all 5 still

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In that case, try this
=Count(DISTINCT {<TableRecId = {"=ActualShipDate <= ShipDate and PickedQty >= (ShipQty/100*$(tol))"}>} TableRecId) / Count(DISTINCT TableRecId)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
