Count based on actual delivery date compared to planned delivery date

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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)

[#3]

This works, thanks!