Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Can anyone help me on this?
Hi Martin,
Can you please tell me, how can i link this fields with Master Calendar..
Can you please provide sample script if possible.
Have a look at this sample script posted by Jagan Mohan
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
Is this something you are looking for?
Let me know
Thank you
Suraj
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));
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