Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
andersonc
Contributor
Contributor

Calendar Variable Min/Max Date Getting Null Values

I believe the min and max variables are showing null values and it's causing syntax errors (unexpected token) in the script. The script is below. Does anyone know what could be causing the errors? It worked fine in QV 11 and stopped with the update to QV 12. 

//Main Calendar Min & Max Date

UNQUALIFY *;

[Temp]:
LOAD
makedate($(v_StartYear),1,1) as [MinDate],
makedate($(v_CurrentYear),12,31) as [MaxDate]
autogenerate 1;

LET v_MinDate = Num (Peek ('MinDate', 0, [Temp]));
LET v_MaxDate = Num (Peek ('MaxDate', 0, [Temp])) ;

// Create a table of all of the dates between the Min and Max dates.

[TempCalendar1]:
LOAD
$(v_MinDate) + RowNo () - 1 as Num,
Num (Date ($(v_MinDate) + RowNo () - 1)) as TempDate
AutoGenerate
$(v_MaxDate) - $(v_MinDate) + 1;

//Main Calendar

[Calendar]:
LOAD
TempDate as DATE_KEY,
TempDate as Date,
Date (TempDate, 'MM/DD/YY') as DateString,
Date (TempDate, 'MM/DD/YY') as DateString2,
Day (TempDate) as Day,
Week (TempDate) as Week,
Date(WeekStart(TempDate),'MMM-D-YY') as WeekStart,
Date(weekstart(TempDate,0,6),'MMM-D-YY') as WeekStartMon, 
Dual (Month (TempDate), Num (Month (TempDate))) as Month,
Num (MonthStart (TempDate)) as [Month Start],
Num (Month (TempDate)) as [Month Num],
'Q' & Ceil (Month (TempDate) / 3) as Quarter,
'Q' & Ceil (Month (TempDate) / 3) & '-' & Year (TempDate) as [Qtr YYYY],
Year (TempDate) as Year,
Date (MonthStart (TempDate), 'MMM YYYY') as [Month YYYY],
Year (TempDate) & '-' & right('00' & Num (Month (TempDate),'#0'),2) as [Year_Month],
Date (MonthStart (TempDate), 'MMM YY') as [Month YY],
(TempDate - YearStart (TempDate) + 1) as [Day In Year],
makedate(Year (TempDate), Num (Month (TempDate)),1) as [Month Begin Date],
1 as [Day Count Ind] ,
dual(Year (TempDate) & ' - Wk ' & Week (TempDate), (Year (TempDate) * 100) + Week(TempDate) )as Year_Week,
Year(TempDate) & '-' & num(Week(TempDate),'00') as YearWeek,
if(TempDate >= $(v_Last5Days) and TempDate < $(v_Today), 1, 0) as Last5DaysFlag,
if(TempDate >= $(v_Last3Months) and TempDate < $(v_Today), 1, 0) as Last3MonthsFlag,
if(TempDate >= $(v_3MonthsPrevYr) and TempDate < $(v_DayinPrevYr), 1, 0) as Same3MonthsPrevFlag,
if(TempDate >= $(v_Closed3Mth_Start) and TempDate <= $(v_Closed3Mth_End), 1, 0) as Closed3MonthsCFlag,
if(TempDate >= $(v_Closed3MthP_Start) and TempDate <= $(v_Closed3MthP_End), 1, 0) as Closed3MonthsPFlag,
if(TempDate >= $(v_Last12Months) and TempDate < $(v_Today), 1, 0) as Last12MonthsFlag,
if(TempDate >= $(v_Last5Weeks) and TempDate < $(v_Today), 1, 0) as Last5WeeksFlag,
if(TempDate <= $(v_ClosedPeriod), 1, 0) as ClosedPeriodFlag,
if(TempDate >= $(v_Fin_TTM) and TempDate <= $(v_ClosedPeriod), 1, 0) as Fin_TTMFlag,
if(TempDate >= $(v_Fin_T6M) and TempDate <= $(v_ClosedPeriod), 1, 0) as Fin_T6MFlag,
if(TempDate >= $(v_Fin_T3M) and TempDate <= $(v_ClosedPeriod), 1, 0) as Fin_T3MFlag,
if(TempDate >= $(v_Ops_TTM) , 1, 0) as Ops_TTMFlag,
if(TempDate >= $(v_Ops_T6M) , 1, 0) as Ops_T6MFlag,
Weekday(TempDate) as WeekDayName
Resident
[TempCalendar1]
Order By
TempDate ASC;

DROP Table [TempCalendar1];
DROP Table [Temp];

1 Solution

Accepted Solutions
Brett_Bleess
Former Employee
Former Employee

Chris, there were some changes in the scripting side of things in the 12.10 track and later, the following Help link will get you on the right track regarding things I believe:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/scrip...

Be sure to read the following section, as I suspect this may be the main reason, depending upon which version of QlikView you are running, you may have an option to turn things off, but in 12.30 and later, this is no longer possible to do...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Backu...

Article regarding turning off the BNF feature: BNF functionality

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

3 Replies
Claudiu_Anghelescu
Specialist
Specialist

I defined the variables v_StartYear and v_CurrentYear, the error has been passed but I have a new error becasue are missing the variables under my comment in script.

 

 

To help community find solutions, please don't forget to mark as correct.
Brett_Bleess
Former Employee
Former Employee

Chris, there were some changes in the scripting side of things in the 12.10 track and later, the following Help link will get you on the right track regarding things I believe:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/scrip...

Be sure to read the following section, as I suspect this may be the main reason, depending upon which version of QlikView you are running, you may have an option to turn things off, but in 12.30 and later, this is no longer possible to do...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Backu...

Article regarding turning off the BNF feature: BNF functionality

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
andersonc
Contributor
Contributor
Author

That's it! Thank you Brett.