topic Re: Orders Count in QlikView

Orders Count

Mon, 15 Feb 2016 09:04:49 GMT

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

Re: Orders Count

Anonymous — Mon, 15 Feb 2016 09:29:15 GMT

load

week([Item Order date]) as week,

Year([Item Order date]) as Year,

Month([Item Order date]) as Month

'Q'&ceil((Month)/3) as Quarter

Dimension

Week

In expression

sum({<week=>}[Item Dispatch date])

Re: Orders Count

Anonymous — Mon, 15 Feb 2016 09:30:05 GMT

load

week([Item Order date]) as week,

Year([Item Order date]) as Year,

Month([Item Order date]) as Month

'Q'&ceil((Month)/3) as Quarter

Dimension

Week

In expression

sum({<week=>}ID)

Re: Orders Count

rubenmarin — Mon, 15 Feb 2016 09:51:18 GMT

Hi john, to better understand... an ID can be counted in many different weeks?

For example, the firsts rows of the excel:

Item Dispatch date	Item Order date	ID
19/05/1978	15/06/1987	S348088
26/05/1981	30/11/1981	K293608

So it's like counting how many orders were "active" in each week?

Re: Orders Count

Anonymous — Mon, 15 Feb 2016 09:55:14 GMT

I think for this got to script, create a week end date like.

Date(Weekend([Item Order date]),'DD/MMM/YYYY') as WeekEndDate

And then try expression like?

=count({<WeekEndDate, WeekEndDate={'>=$(=date([Item Dispatch date],'DD/MMM/YYYY'))

<=$(=date([Item Order date],'DD/MMM/YYYY'))'}>} distinct ID)

Re: Orders Count

Anonymous — Mon, 15 Feb 2016 09:56:18 GMT

load

Week(item Dispatch date) as week

In Expression

Count(ID)

IN dimension

Take week

Re: Orders Count

Mon, 15 Feb 2016 10:10:03 GMT

Yes Correct Ruben Marin

Re: Orders Count

PradeepReddy — Mon, 15 Feb 2016 10:17:43 GMT

which weekend date we have to consider [Item Dispatch date] or [Item Order date]?

Edit: just asking to know, how this is possible the 'Item Dispatch Date' < 'Item Order Date'

Re: Orders Count

Mon, 15 Feb 2016 10:27:06 GMT

Thanks Pradeep for you prompt response.

the condition should be count of ID

Edit: when Week end date >Item Dispatch Date. and Item Dispatch Date>Week Start Date ,

Week end date >Item Order Date. and Item Order Date>Week Start Date ,

Regards

John

Re: Orders Count

tyagishaila — Mon, 15 Feb 2016 10:45:03 GMT

Try this expression,

count({ $<WeekEndDate = {">=$(=Date(ItemOrderDate))<=$(=Date(ItemDispatchdate))"}>} OrderID)

Re: Orders Count

rubenmarin — Mon, 15 Feb 2016 12:19:24 GMT

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.