Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pradeepm
Contributor II
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
Luminary Alumni
Luminary Alumni

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
Contributor II
Contributor II
Author

Can anyone help me on this?

pradeepm
Contributor II
Contributor II
Author

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
Luminary Alumni
Luminary Alumni

Have a look at this sample script posted by Jagan Mohan

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

JonnyPoole
Employee
Employee

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

Is this something you are looking for?

Let me know

Thank you

Suraj

maxgro
MVP
MVP

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

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;

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