topic Re: Count based on actual delivery date compared to planned delivery date in App Development

Count based on actual delivery date compared to planned delivery date

Wed, 21 Dec 2016 16:11:54 GMT

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.

Re: Count based on actual delivery date compared to planned delivery date

Gysbert_Wassenaar — Wed, 21 Dec 2016 16:51:07 GMT

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)

Re: Count based on actual delivery date compared to planned delivery date

Thu, 22 Dec 2016 10:59:13 GMT

This works, thanks!