topic Re: Count on if condition in QlikView

Count on if condition

bnelson111 — Sat, 16 Nov 2024 21:30:45 GMT

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.

Re: Count on if condition

sunny_talwar — Tue, 29 Jan 2019 14:42:30 GMT

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)

Re: Count on if condition

bnelson111 — Tue, 29 Jan 2019 14:51:34 GMT

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

Re: Count on if condition

sunny_talwar — Tue, 29 Jan 2019 14:55:28 GMT

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)

Re: Count on if condition

bnelson111 — Tue, 29 Jan 2019 14:56:19 GMT

I also have a unique identifier TableRecId

Re: Count on if condition

bnelson111 — Tue, 29 Jan 2019 14:58:12 GMT

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

Re: Count on if condition

sunny_talwar — Tue, 29 Jan 2019 15:11:30 GMT

In that case, try this

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

Re: Count on if condition

bnelson111 — Tue, 29 Jan 2019 16:10:36 GMT

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

Re: Count on if condition

sunny_talwar — Tue, 29 Jan 2019 16:23:11 GMT

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.

Re: Count on if condition

bnelson111 — Wed, 30 Jan 2019 10:07:35 GMT

BAck on now, will have a look, thank you