Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
MVP & Luminary
MVP & Luminary

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

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
2 Replies
MVP & Luminary
MVP & Luminary

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

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

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

This works, thanks!