topic Re: Date issue in QlikView

Date issue

Anonymous — Mon, 15 Sep 2014 17:10:05 GMT

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?

Re: Date issue

simondachstr — Mon, 15 Sep 2014 17:23:45 GMT

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.

Re: Date issue

Anonymous — Mon, 15 Sep 2014 17:25:23 GMT

Can anyone help me on this?

Re: Date issue

Anonymous — Mon, 15 Sep 2014 17:28:11 GMT

Hi Martin,

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

Can you please provide sample script if possible.

Re: Date issue

simondachstr — Mon, 15 Sep 2014 17:30:49 GMT

Have a look at this sample script posted by Jagan Mohan

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

Re: Date issue

JonnyPoole — Mon, 15 Sep 2014 17:31:47 GMT

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

Re: Date issue

Mon, 15 Sep 2014 17:53:22 GMT

Is this something you are looking for?

Let me know

Thank you

Suraj

Re: Date issue

maxgro — Mon, 15 Sep 2014 22:06:54 GMT

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));

Re: Date issue

MarcoWedel — Mon, 15 Sep 2014 22:43:07 GMT

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