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.
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.
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.
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.
