Orders Count

[#1]

Hi Qlikheads

I have a table with 3 fields with

[Item Dispatch date],

[Item Order date],

ID,

by using these 3 fields I would like to calculate the total orders for every week ,  the count of id should take when

when the week end date exists between Item Dispatch Date and Item Order date.

how to get this done, can any one help me on this.

Regards

John

[rubenmarin]

Hi john this script seems can work:

Orderonthelist:
LOAD Date([Item Dispatch date]) as [Item Dispatch date],
    If(IsNull([Item Order date]),Today(), Date([Item Order date])) as [Item Order date],
    ID
FROM
[.\OrdersCount.xlsx]
(ooxml, embedded labels, table is Order)
Where not IsNull([Item Dispatch date]);

Calendar:
LOAD *,
    Date(Floor(WeekEnd(Date))) as WeekEnd;  
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min([Item Dispatch date]) as MinDate,
    Max([Item Order date]) as MaxDate
Resident Orderonthelist;

IntervalMatch (WeekEnd) LOAD [Item Dispatch date], [Item Order date] resident Orderonthelist;
Left Join
LOAD [Item Dispatch date], [Item Order date], ID Resident Orderonthelist;

DROP Table Orderonthelist;

But the result is huge, that's because the big gaps between the two dates (Why those big gaps?Is this company serving to Mars?? ).

Btw, I'm filling those without an [Item Order date] with Today date, that makes his huge, because there are from the 80's without [Item Order date], wich returns thousands of weekends for each ID. Check it and then we'll see if this can be optimized.