Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
This works, thanks!