Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pradeepm
New Contributor II

Date issue

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?

8 Replies
simondachstr
Valued Contributor III

Re: Date issue

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.

pradeepm
New Contributor II

Re: Date issue

Can anyone help me on this?

pradeepm
New Contributor II

Re: Date issue

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
Valued Contributor III

Re: Date issue

Have a look at this sample script posted by Jagan Mohan

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

Employee
Employee

Re: Date issue

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

Not applicable

Re: Date issue

Is this something you are looking for?

Let me know

Thank you

Suraj

MVP
MVP

Re: Date issue

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

1.png

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

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;

QlikCommunity_Thread_133379_Pic1.JPG.jpg

QlikCommunity_Thread_133379_Pic2.JPG.jpg

QlikCommunity_Thread_133379_Pic3.JPG.jpg

QlikCommunity_Thread_133379_Pic4.JPG.jpg

hope this helps

regards

Marco

Community Browser