Grouping dates across columns

[#1]

Hi everyone.

Let's say I have a table that looks like this, a result of joining tables with different dates based on the Cust_No:

Cust_No = Customer Number

Ord_Plcd_Dt = Order Placed Date

Ord_Shp_Dt = Order Shipped Date

Ord_Dlv_Dt = Order Delivered Date

Cust_No    Ord_Plcd_Dt    Ord_Shp_Dt    Ord_Dlv_Dt 

--------------------------------------------------------------------------------------------------------

1               03/14/2015      04/18/2015        04/27/2015

1               04/04/2015      04/18/2015        04/27/2015

2               07/13/2015      07/20/2015        07/25/2015

2               07/13/2015      09/14/2015        09/17/2015

2               09/09/2015      07/20/2015        07/25/2015

2               09/09/2015      09/14/2015        09/17/2015

Is it possible that this data can be displayed in a Qlikview chart so that shipped dates and delivered dates are only shown when they are greater than or equal to the most recent previous order date? If that didn't make sense, this is the result I'm looking for:

Cust_No    Ord_Plcd_Dt    Ord_Shp_Dt    Ord_Dlv_Dt 

--------------------------------------------------------------------------------------------------------

1               03/14/2015      NULL                NULL

1               04/04/2015      04/18/2015        04/27/2015

2               07/13/2015      07/20/2015        07/25/2015

2               09/09/2015      09/14/2015        09/17/2015

For customer 1, the 4/18 shipped date and 4/27 delivered date only appear on the line with the 4/04 order date since that is the closest order placed date that is before those dates. For customer 2, the 7/20 shipped date and 7/25 delivered dates appear on the line with the 7/13 order placed date because those dates are after 7/13 but before 9/09. The 9/14 shipped date and the 9/17 delivered date appear on the line with the 09/09 order placed date since that is the closest order placed date before those dates.

Basically, per customer, the dates should be in order across columns and up and down the rows. Can this be achieved with Qlikview?

[Gysbert_Wassenaar]

See attached qvw.

[#3]

This worked for me for the most part. But how would you handle this for an Order Placed Date where the order hasn't yet to be shipped or delivered? So an Order Placed Date which would have two nulls fields, like below:

Cust_No    Ord_Plcd_Dt    Ord_Shp_Dt    Ord_Dlv_Dt

--------------------------------------------------------------------------------------------------------

1               03/14/2015      NULL                NULL

1               04/04/2015      04/18/2015        04/27/2015

2               07/13/2015      07/20/2015        07/25/2015

2               09/09/2015      09/14/2015        09/17/2015

2               11/13/2015      NULL                NULL

The example you provided seems to eliminate such dates because of the where statement.

[#4]

Do you know if something like I mentioned in my above post is possible? I've been trying to figure it out all weekend but I'm seriously stuck.

[Gysbert_Wassenaar · Accepted solution]

See attached example

[#6]

Thank you!