Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Master/Canonical Calendar & Calendar Object

Hello,

I have two different date fields. I am trying to use a Master/Canonical Calendar to show date range in Calendar Object (StartDate, EndDate).

I created the attached QVW sample base on Rob's  Linking to two or more dates sample.

I can connect the "Date" field from Master Calendar to StartDate/EndDate Calendar Object. However, the result is incorrect. Because the "Date" field need to be used with "DateType" field. I need to add "DateType" to differentiate "OrderDate" and "ShipDate".

I tried to add =if(DateType = 'Order',Date)  into "Variable Event Triggers". But then my calendar object cannot work. I also tried to add the code =if (DateType = 'Order',min({1}Date)) into Calendar Object. But it doesn't work as well.

Can anyone who is familiar with this issue let me know how can I solve this issue?

Thanks in advance,

Becky

12 Replies
NickHoff
Valued Contributor

Re: Master/Canonical Calendar & Calendar Object

Why not just add OrderDate and ShipDate to your master calendar by doing Date AS OrderDate, Date AS ShipDate?  Then you won't need the date type.  You can get rid of the linked table if you go about it that way.

Not applicable

Re: Master/Canonical Calendar & Calendar Object

Hi Nick,

Thanks for your response and idea.

I can get it work without using a Master Calendar. Just use OrderDate and ShipDate seperately with each Calendar Object associate with Variables. But after I complished it, I am now thinking if I can only use one data field but different DateType attributes to complete my goal. Just trying to find a better/simple way to do it...

NickHoff
Valued Contributor

Re: Master/Canonical Calendar & Calendar Object

You would still use the Master Calendar, just add OrderDate and ShipDate to the master calendar aliased from Date.

So it would be:

Date AS OrderDate,

Date AS ShipDate,

In your Master Calendar, the only thing you are doing is getting rid of the linked table, which is causing the issue by having both dates in one field which is Date.

Not applicable

Re: Master/Canonical Calendar & Calendar Object

I am not sure how to do it for the entire script. But I can add:

CALL CalendarFromField('OrderDate', 'OrderCalendar', 'Order '); // Create Calendar for OrderDate
CALL CalendarFromField('ShipDate', 'ShipCalendar', 'Ship ');
// Create Calendar for ShipDate

Then I will get different master calendars with seperate OrderDate and ShipDate.  I believe it can perform as the same fuction as you mentioned, correct?

NickHoff
Valued Contributor

Re: Master/Canonical Calendar & Calendar Object

That's not what i'm suggesting.  You are making it harder for yourself going about it that way.  Just keep your Order and Shipment table left alone in the original form.  Then on the master calendar table CommonCalendar, just add

Date AS OrderDate,

Date AS ShipDate,

IF you need to create a master calendar feel free to use the following script I added the OrderDate and ShipDate to it for you, you may have to change your fiscal dates, and min/max if you need them.

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1)); 
//LET vDateMax = Floor(MonthEnd(Today())); 
LET vDateMax = Num(MakeDate(2020,6,30)); 
LET vDateToday = Num(Today()); 
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6;   //offset for calculating fiscal year

TempCalendar: 
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

SCM_CALENDAR_MASTER: 
LOAD
Date(TempDate) AS CalendarDate
Date(TempDate) AS  %DATE_KEY,

Date(TempDate) AS OrderDate,

Date(TempDate) AS ShipDate,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth
WeekDay(TempDate) AS CalendarDayName
Week(TempDate) AS CalendarWeekOfYear
Month(TempDate) AS CalendarMonthName
'Q' &
Ceil(Month(TempDate)/3) AS CalendarQuarter
Year(TempDate) AS CalendarYear

// Calendar Date Names 
WeekName(TempDate) as CalendarWeekNumberAndYear
MonthName(TempDate) as CalendarMonthAndYear
QuarterName(TempDate) as CalendarQuarterMonthsAndYear

// Start Dates 
DayStart(TempDate) as CalendarDayStart
WeekStart(TempDate) as CalendarWeekStart
MonthStart(TempDate) as CalendarMonthStart
QuarterStart(TempDate) as CalendarQuarterStart
YearStart(TempDate) as CalendarYearStart

// End Dates 
DayEnd(TempDate) as CalendarDayEnd
WeekEnd(TempDate) as CalendarWeekEnd
MonthEnd(TempDate) as CalendarMonthEnd
QuarterEnd(TempDate) as CalendarQuarterEnd
YearEnd(TempDate) as CalendarYearEnd

// Combo Date Examples 
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter
'Wed ' &
DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' &
Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' &
Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC

DROP TABLE TempCalendar; 

NickHoff
Valued Contributor

Re: Master/Canonical Calendar & Calendar Object

I just realized this would create a circular link,  I see what you are trying to accomplish.  Which you'd correct by using the following:

OrderID2OrderDate:

     Mapping Load OrderID, OrderDate From Orders;


OrderID2ShippingDate:

       Mapping Load OrderID, ShipDate From Shipping ;

     Load OrderID, Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate, 'Order' as DateType

          Resident Order;

       Load OrderID, ShippedDate as CanonicalDate, 'Shipped' as DateType

Re: Master/Canonical Calendar & Calendar Object

I am not 100 % sure what the issue here? Are you looking to create two different start and end dates here?

Order Start Date

Order End Date

Shipment Start Date

Shipment End Date

Is that the goal? I am not sure I understand the final goal here. Can you may be provide more details?

Not applicable

Re: Master/Canonical Calendar & Calendar Object

Hi Sunny,

I created two different start and end dates here. One for Order, the other is for Shipment.

My goal is trying to use only one date field (Date) to enable both Order calendar objects and Shipment calendar objects working. I am currently using two date fields (OrderDate, ShipDate) to make those calendar objects work.

In addition, a werid thing is the results came back were different.  I got different results, with or without using master calendar to make calendar objects work.

Not applicable

Re: Master/Canonical Calendar & Calendar Object

Thanks Nick. I am just not sure how to use one date filed ("Date" in my example, "CanonicalDate" in your example) to make this work.

Best

Becky