topic Re: Grouping dates across columns in QlikView

Grouping dates across columns

Thu, 12 Nov 2015 03:23:08 GMT

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?

Re: Grouping dates across columns

Gysbert_Wassenaar — Thu, 12 Nov 2015 07:04:46 GMT

See attached qvw.

Re: Grouping dates across columns

Fri, 13 Nov 2015 23:27:42 GMT

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.

Re: Grouping dates across columns

Sun, 15 Nov 2015 23:48:56 GMT

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.

Re: Grouping dates across columns

Gysbert_Wassenaar — Mon, 16 Nov 2015 13:18:25 GMT

See attached example

Re: Grouping dates across columns

Mon, 16 Nov 2015 18:18:44 GMT

Thank you!