Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables and in those two tables i use two different dates.
So i want to use common date in date filters.
say my tow dates are
Mydate in mytable and machinedate in machine table.
I want to use common date in my calander
Thanks
Anuradha,
Follow this:
http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
Thanks,
AS
Hi Anuradha
Try to use a MasterCalendar like this:
LET varMinDate = StartDate; eg:'6/15/2001'
LET varMaxDate = EndDate; eg: Today();
//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
//*************Master Calendar************
MasterCalendar:
LOAD
TempDate AS Date,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear
Resident TempCalendar
Order by TempDate ASC;
Drop Table TempCalendar;
However if you want 2 different dates for analysis, you need 1 calendar per date...
Hope this is helpful for you!
BRGDS,
Anthony
It seems you want to associate both the tables based on the date fields.
May be you can try the code below.
// create a table by loading distinct dates from both the tables and join
Tab1:
load OtherFields1,[Date Field1] as [Date Field] from tab1;
Tab2:
load OtherFields2,[Date Field2] as [Date Field] from tab2;
TempCalendar:
load Distinct [Date Field] Resident Tab1;
Join LOAD Distinct [Date Field] Resident Tab2;
MainCalendar:
load [Date Field] Resident TempCalendar;
DROP Table TempCalendar;