Calendar Variable Min/Max Date Getting Null Values

andersonc — Wed, 25 Nov 2020 16:16:04 GMT

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];

Re: Calendar Variable Min/Max Date Getting Null Values

Claudiu_Anghelescu — Tue, 06 Aug 2019 10:47:23 GMT

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.

Re: Calendar Variable Min/Max Date Getting Null Values

Brett_Bleess — Tue, 20 Aug 2019 12:13:47 GMT

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/script-syntax.htm

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/Backus-Naur-formalism.htm

Article regarding turning off the BNF feature: BNF functionality

Regards,
Brett

Re: Calendar Variable Min/Max Date Getting Null Values

andersonc — Tue, 20 Aug 2019 12:56:56 GMT

That's it! Thank you Brett.

topic Re: Calendar Variable Min/Max Date Getting Null Values in QlikView

Calendar Variable Min/Max Date Getting Null Values

Re: Calendar Variable Min/Max Date Getting Null Values

Re: Calendar Variable Min/Max Date Getting Null Values

Re: Calendar Variable Min/Max Date Getting Null Values