Discussion Board for collaboration related to QlikView App Development.
I have 2 fields i.e orderdate, deliverydate.
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:
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.
This is an excellent link to show a master calendar and also keep both date types as a dimension
I think you can solve it with a date (month) island and a chart with expression as in image
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
load distinct MonthStart(Orderdate) as Date Resident source;
Concatenate (dateisland) load distinct MonthStart(Deliverydate) as Date Resident source where not Exists(Date, MonthStart(Deliverydate));
one possible solution would be to create a canonical date like:
RecNo() as ID
CrossTable (DateType, Date)
LOAD ID, Orderdate, deliverydate
Left Join (tabCalendar)
Month(Date) as Month
hope this helps