Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Magnus
Contributor
Contributor

calculate number of shipments with atleast one delayed package

I have the following rules:

When a car is loaded with packages it gets a unique shipmentID. When the car is done with the delivery route and is reloaded it gets a new shipmentID.

Every package has a unique packageID. And every packageID has a PromisedDeliveryDate and DeliveredDate.

A shipmentID can have multiple packageID, but a packageID can only be connected to one shipmentID.

I can already successfully calculate how many packageID that are delayed by this:

Count({<packageID={"=PromisedDeliveryDate < DeliveredDate"}>}packageID)

 

Now I want to calculate how many shipmentID that had atleast one delayed packageID connected to it. 

I have tried the following but it doesn't get the right result

Count({<packageID={"=PromisedDeliveryDate < DeliveredDate"}>}DISTINCT shipmentID)

3 Replies
NhanNguyen
Contributor III
Contributor III

Count({<packageID={"=PromisedDeliveryDate < DeliveredDate"}>}packageID) - if this set analysis can give you the packageID, why don't you use it? I could see from the 2nd set analysis DeliveredEventDate was used instead of DeliveredDate. Might it be the cause?

Magnus
Contributor
Contributor
Author

I do use it. It gives my the total amount of delayed packages. But that is not what I am trying to do. I want to see how many cars (shipments) that had delayed packages on them. That's not the same thing. The "DeliveredEventDate" was a just me writing wrong, already edited.
NhanNguyen
Contributor III
Contributor III

I know that you want to count the number of shipments. I mean if the first function could give you the PackageIDs that were delayed then it should do the job in the 2nd expression. However, I notice a difference in the field names, so I raised it up.
I assume that you could try this: Count({<shipmentID = {"=PromisedDeliveryDate < DeliveredDate"}>}DISTINCT shipmentID).