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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count based on actual delivery date compared to planned delivery date

Hi,

How do i make a count (that complies to selections) on 2 date fields. I have a DATERECEIVED field which is the actual deliverydate and a DELIVERYDATE which is the planned deliverydate. My goal is to get a % of the orders that are delivered on time.

My plan was to make a

=Count(statement that selects where DATERECEIVED < DELIVERYDATE) / Count (all deliveries)

i cant figure out how to formulate the statement.

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Perhaps this:

Count( {<DeliveryID={"=DATERECEIVED < DELIVERYDATE"}>} DeliveryID) / Count(total DeliveryID)

But if date both fields exist in the same table you create in the script then you should simply create a flag field in that same table:

Deliveries:

LOAD

     DeliveryID,

     DATERECEIVED,

     DELIVERYDATE,

     If(DATERECEIVED < DELIVERYDATE, 1, 0) as DeliveredOnTime

     ...other fields...

FROM

     ...source table...

     ;

Your count expression will get a lot simpler with that new field:

Count( {<DeliveredOnTime={1}>} DeliveryID) / Count(total DeliveryID)


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Perhaps this:

Count( {<DeliveryID={"=DATERECEIVED < DELIVERYDATE"}>} DeliveryID) / Count(total DeliveryID)

But if date both fields exist in the same table you create in the script then you should simply create a flag field in that same table:

Deliveries:

LOAD

     DeliveryID,

     DATERECEIVED,

     DELIVERYDATE,

     If(DATERECEIVED < DELIVERYDATE, 1, 0) as DeliveredOnTime

     ...other fields...

FROM

     ...source table...

     ;

Your count expression will get a lot simpler with that new field:

Count( {<DeliveredOnTime={1}>} DeliveryID) / Count(total DeliveryID)


talk is cheap, supply exceeds demand
Not applicable
Author

This works, thanks!