Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar Problem

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;

4 Replies
swuehl
MVP
MVP

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

Not applicable

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.