Calculating On-time Delivery when a Customer order has scheduled delivery dates

[ben_skerrett]

Hi Gurus,

I am hoping someone can please help.

In the below example we have an order (order number = 9999) from a customer.

The customer is ordering 3 different Products with varying delivery dates.

The below table shows (in Blue) the scheduled quantity to be delivered and the quantity.

In Green it shows how many were delivered and the actual delivery date.

I am struggling to create the "Result" column.

I need a way of identifying the first actual delivery date and comparing it against the first scheduled delivery date, then the second actual delivery needs comparing to the second scheduled delivery... and so on.

To do this i would need a way to create individual lines  as per below.

To start with I tried using iterNo() but the below script creates an IterNo() per delivery date (the lines in red should be 1 to 6 not, 1-2, 1-2, 1-2.

temp:
load * Inline [
Product , Qty , Del_Date
A , 2 , 01/30/2024
B , 1 , 01/31/2024
C , 2 , 02/01/2024
C , 2 , 02/02/2024
C , 2 , 02/03/2024
];

New:
Load
Product,
date(Del_Date,'YYYY-MM-DD') as Del_Date,
Qty,
IterNo() as Counter
Resident temp

While IterNo()<= Qty;
drop table temp;

Thanks in Advance

Ben

[Jorge_FR]

Hello, why not compare the dates directly? Otherwise, have you taken a look at this link? https://community.qlik.com/t5/QlikView-App-Dev/How-to-compare-two-date-values/td-p/932610

[ben_skerrett]

Thank you for the reply.

The difficulty I am having is the data source (SAP) has scheduled sale order line items

i.e. I can have 1 line for 2 off with the same contractual delivery date but I can delivery both items on different dates. There is no one to one relationship for me to compare dates.

I need to find a way to have the data showing as per the second screen shot, i.e. each line is a quantity of 1. Then I need to find a way to ensure that the first delivered date is compared to the first scheduled delivery date, and so on. Thanks again Ben

[Jorge_FR]

I see @ben_skerrett,

Please Try:

temp:

LOAD * INLINE [

    Product, Qty, Del_Date

    A, 2, 01/30/2024

    B, 1, 01/31/2024

    C, 2, 02/01/2024

    C, 2, 02/02/2024

    C, 2, 02/03/2024

];

TotalQty:

Load

    Product,

    sum(Qty) as Total_Order_Qty

Resident temp

Group By Product;

Left Join (temp)

Load

    Product,

    Total_Order_Qty

Resident TotalQty;

New:

Load

IterNo() AS Counter,

    Product,

    date(Del_Date,'YYYY-MM-DD') as Del_Date,

    Qty,

    Total_Order_Qty

Resident temp

While IterNo() <= Total_Order_Qty;

drop table temp;

drop table TotalQty;

[ben_skerrett]

Hi Jorge,

Thank you for the comment.

Your script is so close. There is an issue with product C when I add the delivery date field. Each of the lines duplicate for each of the delivery dates.

The lines crossed out should not be visible. Thanks again

[ben_skerrett · Accepted solution]

I managed to solve the problem with the following. Thanks

temp:
LOAD * INLINE [
Product, Qty, Del_Date
A, 2, 01/30/2024
B, 1, 01/31/2024
C, 2, 02/01/2024
C, 2, 02/02/2024
C, 2, 02/03/2024
];

TMP:
Load

Product,
date(Del_Date,'YYYY-MM-DD') as Del_Date,
Qty,
IterNo() AS Counter

Resident temp
While IterNo() <= Qty;
drop table temp;

TABLE:
NoConcatenate
LOAD *,
If(Product = Previous(Product),
RangeSum(Peek('Rank'), 1),1) as Rank

Resident TMP
Order By Product, Del_Date asc;
drop table TMP;