# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Valued Contributor II

## Count late delivery type Stop only if pickup Stop was On-Time

Hello Everyone,

I have a problem where I am attempting to answer the following question:

"Count the late delivery stops, ONLY when the associated pickup stop was on-time"

For instance, here is a sample script:

```Stops:
[
'OrderNumber', 'StopNumber', 'Action', 'WasLate'
1, 101, Pickup, Y
1, 102, Delivery, N
1, 103, Delivery, Y

2, 201, Pickup, N
2, 202, Delivery, Y
2, 203, Delivery, Y

3, 301, Pickup, N
3, 302, Delivery, Y
3, 303, Delivery, N
]

```

In this instance, a following Table would have the following results.

Order Number | Late Stops

1     ,     0

2     ,     2

3     ,      1

The reason the OrderNumber "1" has a LateStop count of 0 is because the Pickup was late, so we don't count those Late Deliveries (Stop Number=103).

In SQL I would say something like this:

```SELECT COUNT(*) FROM Stops s
WHERE s.WasLate = 'Y'
AND s.OrderNumber NOT IN
(
SELECT OrderNumber FROM Stops late
WHERE late.Action = 'Pickup'
AND late.WasLate = 'Y'
)

```

Any help on this is greatly appreciated.

1 Solution

Accepted Solutions
MVP

## Re: Count late delivery type Stop only if pickup Stop was On-Time

Try this expression

=Count({<WasLate = {'Y'}, OrderNumber = E({<Action = {'Pickup'}, WasLate = {'Y'}>})>}StopNumber)

3 Replies
MVP

## Re: Count late delivery type Stop only if pickup Stop was On-Time

Try this expression

=Count({<WasLate = {'Y'}, OrderNumber = E({<Action = {'Pickup'}, WasLate = {'Y'}>})>}StopNumber)

Highlighted
Valued Contributor II

## Re: Count late delivery type Stop only if pickup Stop was On-Time

Or do something like this in the script:

MapPUStops:

OrderNumber,

WasLate

Resident Stops

where Action='Pickup'

;

noconcatenate

StopsRestate:

OrderNumber,

StopNumber,

Action,

if(Action='Pickup','WasLate,

if(Action='Delivery' and PULate='Y','N',WasLate)) as WasLate

;