Master Calendar Problem

[#1]

Hi, i am new to Qlikview. I have created a master calendar but year column which contains 2 years data (2011 & 2012)  is not working properly. It's providing same result for both years on selecting a particular year.

VisitedSites:

LOAD Agency,

     Platform,

     Url,

     [Date Sampled],

   date(floor(timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Date],

time(frac (timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Time],

[Likes/Followers/Visits/Downloads]    

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)where Platform <> 'TOTAL' ;

Temp:

Load

               min([Record_Date]) as minDate,

               max([Record_Date]) as maxDate

Resident VisitedSites;

Let varMinDate = '8/1/2011';

Let varMaxDate ='12/31/2012';

DROP Table Temp;

Datefield:

LOAD

   date(date#('$(varMinDate)')-1 + recno())  as Datefield

AUTOGENERATE (date#('$(varMaxDate)')-date#('$(varMinDate)'))+1;

Calendar:

LOAD

    Datefield as %Date,

    date(Datefield,'YYYY-MMM-DD') as Date,

    year(Datefield) as Year,

    month(Datefield) as Month,

    day(Datefield) as Day,

    week(Datefield) as Week,

    weekday(Datefield) as Weekday,

    'Q' & ceil(month(Datefield) / 3) AS Quarter,

     date(monthstart(Datefield), 'MMM-YYYY') as MonthYear,

     dual((Month(Datefield) &'-'&Year(Datefield)),num(MakeDate(year(Datefield),month(Datefield)))) as Monthyear,

               ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

     yearname (Datefield, 0, 10 ) as FiscalYear

RESIDENT Datefield;

drop table Datefield;

[swuehl]

What is the key field between your calendar and VisitedSites table?

If there is no link, selections in calendar will have no effect on your other tables at all.

[sunny_talwar]

Your date field from calendar isn't linked to the date field in your VisitedSites table. Try this:

Calendar:

LOAD

    Datefield as [Record_Date],

    date(Datefield,'YYYY-MMM-DD') as Date,

    year(Datefield) as Year,

    month(Datefield) as Month,

    day(Datefield) as Day,

    week(Datefield) as Week,

    weekday(Datefield) as Weekday,

    'Q' & ceil(month(Datefield) / 3) AS Quarter,

     date(monthstart(Datefield), 'MMM-YYYY') as MonthYear,

     dual((Month(Datefield) &'-'&Year(Datefield)),num(MakeDate(year(Datefield),month(Datefield)))) as Monthyear,

               ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

     yearname (Datefield, 0, 10 ) as FiscalYear

RESIDENT Datefield;

drop table Datefield;

[rubenmarin]

Hi Manisha, you need to link your calendar to the VisitedSites table.

Tables are linked when they have fields with the same name,to make it simpler and don't change anything you already loaded, you can create another field in your calendar to link the tables:

Calendar:

LOAD

    Datefield as %Date,

    date(Datefield,'YYYY-MMM-DD') as Date,

    date(floor(timestamp#(Datefield,'MM/DD/YYYY hh:mm'))) as [Record_Date],

   date(Datefield) as [Record_Date]

...

Edit: Trying to keep it simple I made it wrong, Sunny is right, the timestamp# will make my script to fail

[#5]

Thank you all for correcting me. I would like to know what is the best scenario to create variables for dates. As my data as no values for whole year in 2011.