Date issue

[Anonymous]

Hi,

I have 2 fields i.e orderdate, deliverydate.

Orderdate  deliverydate

08/25/2014  08/28/2014

08/26/2014  09/01/2014

08/28/2014  09/02/2014

09/02/2014  09/03/2014

09/02/2014  09/04/2014

based on this fields i need to show the total no.of orders(based on orderdate), total no.of deliveries(based on deliverydate) for a month.

output should be like this:

         No.Orders   No.Deliveries

Aug         3                 1

Sep          2                 4

Can anyone please tell me, do i need to create a master calendar for this or can we create month field based on these fields?

[simondachstr]

This strongly depends if you will have months that have deliveries but no orders, or the other way around months in which you have orders and no deliveries. If that's the case, you'll need a Master Calendar.

[Anonymous]

Can anyone help me on this?

[Anonymous]

Hi Martin,

Can you please tell me, how can i link this fields with Master Calendar..

Can you please provide sample script if possible.

[simondachstr]

Have a look at this sample script posted by Jagan Mohan

http://community.qlik.com/docs/DOC-3858

[JonnyPoole]

This is an excellent link to show a master calendar and also keep both date types as a dimension

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

[#7]

Is this something you are looking for?

Let me know

Thank you

Suraj

[maxgro]

I think you can solve it with a date (month) island and a chart with expression as in image

source:

load * inline [

Orderdate , Deliverydate

08/25/2014 , 08/28/2014

08/26/2014 , 09/01/2014

08/28/2014 , 09/02/2014

09/02/2014 , 09/03/2014

09/02/2014 , 09/04/2014

];

dateisland:

load distinct MonthStart(Orderdate) as Date Resident source;

Concatenate (dateisland) load distinct MonthStart(Deliverydate) as Date Resident source where not Exists(Date, MonthStart(Deliverydate));

[MarcoWedel]

Hi,

one possible solution would be to create a canonical date like:

tabOrders:
LOAD *,
    RecNo() as ID
INLINE [
    Orderdate, deliverydate
    08/25/2014, 08/28/2014
    08/26/2014, 09/01/2014
    08/28/2014, 09/02/2014
    09/02/2014, 09/03/2014
    09/02/2014, 09/04/2014
];

tabCalendar:
CrossTable (DateType, Date)
LOAD ID, Orderdate, deliverydate
Resident tabOrders;

Left Join (tabCalendar)
LOAD Distinct
  Date,
  Month(Date) as Month
Resident tabCalendar;

hope this helps

regards

Marco