topic Calendar Help? in QlikView

Calendar Help?

stuwannop — Wed, 27 Jul 2011 10:44:44 GMT

Hi I have two calendar scripts linking to two different date fields in an excel table (both are identically formatted).

Although the scripts run fine, one of the calendars won't pick up the date field correctly (i.e. When I filter the calendar (e.g. Clicking June) no data appears, although it shows in a straight table).

Below is my script for the second calendar:

Range:
LOAD
 min(EDate) as startdate,
 max(EDate) as enddate
resident [Group Opps];
//Peek out the values for later use
let vStart = peek('startdate',-1,'Range')-1;
let vEnd = peek('enddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);
//Remove Range table as no longer needed
Drop table Range;
//Generate a table with a row per date between the range above
Date:
Load
 $(vStart)+recno() as EDate
autogenerate $(vRange);
//Calculate the Parts you need to examine
EstCalendar:
load
 EDate as EDate,
// date(Date,'dd/mm/yyyy') as Cal_FullDate,
 Year(EDate) as EstCalendarYear,
 'Q'&ceil(Month(EDate)/3) AS EstCal_Quarter,
// right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear,
// if(InYear (Date, $(vToday), -1),1) as Cal_FULL_LY, // All Dates Last Year
// if(InYear (Date, $(vToday), 0),1) as Cal_FULL_TY, // All Dates This Year
    if(InYearToDate (EDate, today(), 0),1,0) as EstCal_YTD_TY,  // All Dates to Date this Year
    if(InYearToDate (EDate, today(), -1),1,0) as EstCal_YTD_LY,  // All Dates to Date Last Year
    
    if(InQuarterToDate (EDate, today(), 0),1,0) as EstCal_QTR_TQ,
    if(InQuarterToDate (EDate, today(), -1),1,0) as EstCal_QTR_LQ,  
    
    if(InMonthToDate(EDate, today(), 0),1,0) as EstCal_MNTH_TM,
    if(InMonthToDate(EDate, today(), -1),1,0) as EstCal_MNTH_LM,  
    
//  YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY)
 quartername(EDate) as EstCal_CalendarQuarter,
// quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter,
// Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal!
 Month(EDate)&'-'&right(year(EDate),4) as EstCal_MonthYear,
 Month(EDate) as EstCal_Month,
 Day(EDate) as EstCal_Day,
 Week(EDate) as EstCal_Week,
 Weekday(EDate) as EstCal_WeekDay
resident Date;
//Tidy up
Drop table Date;

Like I say, script loads fine, dates are displayed in a straight table if I check that and I don't see any reason why I can't run 2 calendars. Could it be something simple like a data format issue - if so how could I correct it? I've checked the excel sheet and it's formatted as a date.

Any ideas anyone?

Thanks in advance

Stu

Calendar Help?

Wed, 27 Jul 2011 13:46:41 GMT

Hi Stu,

take a look at your linking field(s). I suppose it is Date.EDate and let's say OrgDate.EDate. Then check out if both formats fit together. Your

Load $(vStart)+recno() as EDate autogenerate $(vRange);

generates a numeric field. May be a formatting for this like Date($(vStart)+recno()) could help.

Nevertheless I am not happy when linking tables using date-fields. I prefer creating surrogato-keyfields (one per table) with functions like Autonumber(EDate,1) AS $Date_ID.

HtH

Roland

Calendar Help?

stuwannop — Tue, 22 Nov 2011 16:38:04 GMT

Thanks for your reply Roland I'll give that a try.

Stu

Calendar Help?

stuwannop — Tue, 22 Nov 2011 17:01:53 GMT

Hi Roland

I gave it a go (changing the formats to match) but it didn't work - worth a try though. The problem I think is the dates in my source data are appointments, which of course have different times. The script runs but creates about 361,000 calendar values!

I'm quite new to qlikview so I'm still on the the hunt for an answer - any idea how I could modify my script? Essentially I need to be able to create a list box where you can select month/year and only show (or sum)appointments in a specified period. I've been on a couple of QV courses but I don't think I covered surrogate fields - they actually teach you the calendar script though.

Thanks

Stu

Calendar Help?

ToniKautto — Tue, 22 Nov 2011 21:12:04 GMT

Have you checked that your date field is an integer and not a decimal value? If the date's numeric value has a decimal part then it will not match a date. If this is the case then you can easily get around it by applying Floor() to your date value to remove the decimal part.

Calendar Help?

stuwannop — Wed, 23 Nov 2011 17:51:16 GMT

Thanks for your response Toni I will give it a try.

Stu