Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
sum({<week=>}ID)
Hi john, to better understand... an ID can be counted in many different weeks?
For example, the firsts rows of the excel:
So it's like counting how many orders were "active" in each week?
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)
Week(item Dispatch date) as week
In Expression
Count(ID)
IN dimension
Take week
Yes Correct Ruben Marin
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'
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 ,
Try this expression,
count({ $<WeekEndDate = {">=$(=Date(ItemOrderDate))<=$(=Date(ItemDispatchdate))"}>} OrderID)
