Misbehaving Variables

countwise — Mon, 26 Mar 2018 16:07:23 GMT

We are using Qlik Sense Enterprise version 3.1

We have variables that do not seem to refresh correctly

i.e.

vCurrentWeekIntervalFiscalID

Counts how many 15minute periods we are into the current week.

This is currently returning a value of 447 which would make it 15:45 on day 5

It should be returning a value of 48 which would make it 12:00 on day 1 (The current day and time)

The data is reloaded automatically every hour and is currently showing today's time and day (12:00 on Day 1) correctly.

At some point it will correct itself but I have no way of knowing when.

There are several other variables behaving in the same way

An example of the variable in use

Sum(

{1<

[Site_ActiveHours]={'-1'},

WeekFiscalID = {'$(=$(vCurrentWeekFiscalID))'},

WeekIntervalFiscalID = {'<=$(=$(vCurrentWeekIntervalFiscalID))'}

Traffic)

This is from the load script

// Find Current Week

MaxWeekTemp:

LOAD

"Date" AS zCurrentDate,

"WeekDay" AS zCurrentWeekDay,

"WeekStartDateCalendar" AS zCurrentWeekStartDateCalendar,

"WeekStartTextRangeCalendar" AS zCurrentWeekStartTextRangeCalendar,

"WeekStartDateFiscal" AS zCurrentWeekStartDateFiscal,

"WeekStartTextRangeFiscal" AS zCurrentWeekStartTextRangeFiscal,

"WeekCalendar" AS zCurrentWeekCalendar,

"WeekCalendarID" AS zCurrentWeekCalendarID,

"WeekIntervalCalendarID" AS zCurrentWeekIntervalCalendarID,

"WeekFiscal" AS zCurrentWeekFiscal,

"WeekFiscalID" AS zCurrentWeekFiscalID,

"WeekIntervalFiscalID" AS zCurrentWeekIntervalFiscalID,

"MonthCalendar" AS zCurrentMonthCalendar,

"MonthCalendarID" AS zCurrentMonthCalendarID,

"MonthIntervalCalendarID" AS zCurrentMonthIntervalCalendarID,

"MonthFiscal" AS zCurrentMonthFiscal,

"MonthFiscalID" AS zCurrentMonthFiscalID,

"MonthIntervalFiscalID" AS zCurrentMonthIntervalFiscalID,

"QuarterCalendar" AS zCurrentQuarterCalendar,

"QuarterCalendarID" AS zCurrentQuarterCalendarID,

"QuarterIntervalCalendarID" AS zCurrentQuarterIntervalCalendarID,

"QuarterFiscal" AS zCurrentQuarterFiscal,

"QuarterFiscalID" AS zCurrentQuarterFiscalID,

"QuarterIntervalFiscalID" AS zCurrentQuarterIntervalFiscalID,

"YearCalendar" AS zCurrentYearCalendar,

"YearCalendarID" AS zCurrentYearCalendarID,

"YearIntervalCalendarID" AS zCurrentYearIntervalCalendarID,

"YearFiscal" AS zCurrentYearFiscal,

"YearFiscalID" AS zCurrentYearFiscalID,

"YearIntervalFiscalID" AS zCurrentYearIntervalFiscalID,

"Day" AS zCurrentDay,

"DayIntervalID" AS zCurrentDayIntervalID

Resident CALTABLE

WHERE CalendarID = $(vMaxCalendarID);

LET vCurrentDate = NUM(PEEK('zCurrentDate',0,'MaxWeekTemp'));

LET vYesterday = $(vCurrentDate)-1;

LET vCurrentWeekStartDateCalendar = NUM(PEEK('zCurrentWeekStartDateCalendar',0,'MaxWeekTemp'));

LET vCurrentWeekStartTextRangeCalendar = NUM(PEEK('zCurrentWeekStartTextRangeCalendar',0,'MaxWeekTemp'));

LET vCurrentWeekStartDateFiscal = NUM(PEEK('zCurrentWeekStartDateFiscal',0,'MaxWeekTemp'));

LET vCurrentWeekStartTextRangeFiscal = NUM(PEEK('zCurrentWeekStartTextRangeFiscal',0,'MaxWeekTemp'));

LET vCurrentWeekCalendar = NUM(PEEK('zCurrentWeekCalendar',0,'MaxWeekTemp'));

LET vCurrentWeekCalendarID = NUM(PEEK('zCurrentWeekCalendarID',0,'MaxWeekTemp'));

LET vCurrentWeekIntervalCalendarID = NUM(PEEK('zCurrentWeekIntervalCalendarID',0,'MaxWeekTemp'));

LET vCurrentWeekFiscal = NUM(PEEK('zCurrentWeekFiscal',0,'MaxWeekTemp'));

LET vCurrentWeekFiscalID = NUM(PEEK('zCurrentWeekFiscalID',0,'MaxWeekTemp'));

LET vCurrentWeekIntervalFiscalID = NUM(PEEK('zCurrentWeekIntervalFiscalID',0,'MaxWeekTemp'));

LET vCurrentWeekDay = NUM(PEEK('zCurrentWeekDay',0,'MaxWeekTemp'));

If $(vCurrentWeekDay) = 1 Then

LET vReportWeekDay = 7;

LET vReportWeekStartDateCalendar = $(vCurrentWeekStartDateCalendar) - 7;

LET vReportWeekStartDateFiscal = $(vCurrentWeekStartDateFiscal) - 7;

Else

LET vReportWeekStartDateCalendar = $(vCurrentWeekStartDateCalendar);

LET vReportWeekStartDateFiscal = $(vCurrentWeekStartDateFiscal);

LET vReportWeekDay = $(vCurrentWeekDay) - 1;

End If

LET vCurrentMonthCalendar = NUM(PEEK('zCurrentMonthCalendar',0,'MaxWeekTemp'));

LET vCurrentMonthCalendarID = NUM(PEEK('zCurrentMonthCalendarID',0,'MaxWeekTemp'));

LET vCurrentMonthIntervalCalendarID = NUM(PEEK('zCurrentMonthIntervalCalendarID',0,'MaxWeekTemp'));

LET vCurrentMonthFiscal = NUM(PEEK('zCurrentMonthFiscal',0,'MaxWeekTemp'));

LET vCurrentMonthFiscalID = NUM(PEEK('zCurrentMonthFiscalID',0,'MaxWeekTemp'));

LET vCurrentMonthIntervalFiscalID = NUM(PEEK('zCurrentMonthIntervalFiscalID',0,'MaxWeekTemp'));

LET vCurrentQuarterCalendar = NUM(PEEK('zCurrentQuarterCalendar',0,'MaxWeekTemp'));

LET vCurrentQuarterCalendarID = NUM(PEEK('zCurrentQuarterCalendarID',0,'MaxWeekTemp'));

LET vCurrentQuarterIntervalCalendarID = NUM(PEEK('zCurrentQuarterIntervalCalendarID',0,'MaxWeekTemp'));

LET vCurrentQuarterFiscal = NUM(PEEK('zCurrentQuarterFiscal',0,'MaxWeekTemp'));

LET vCurrentQuarterFiscalID = NUM(PEEK('zCurrentQuarterFiscalID',0,'MaxWeekTemp'));

LET vCurrentQuarterIntervalFiscalID = NUM(PEEK('zCurrentQuarterIntervalFiscalID',0,'MaxWeekTemp'));

LET vCurrentYearCalendar = NUM(PEEK('zCurrentYearCalendar',0,'MaxWeekTemp'));

LET vCurrentYearCalendarID = NUM(PEEK('zCurrentYearCalendarID',0,'MaxWeekTemp'));

LET vCurrentYearIntervalCalendarID = NUM(PEEK('zCurrentYearIntervalCalendarID',0,'MaxWeekTemp'));

LET vCurrentYearFiscal = NUM(PEEK('zCurrentYearFiscal',0,'MaxWeekTemp'));

LET vCurrentYearFiscalID = NUM(PEEK('zCurrentYearFiscalID',0,'MaxWeekTemp'));

LET vCurrentYearIntervalFiscalID = NUM(PEEK('zCurrentYearIntervalFiscalID',0,'MaxWeekTemp'));

LET vCurrentDay = NUM(PEEK('zCurrentDay',0,'MaxWeekTemp'));

LET vCurrentDayIntervalID = NUM(PEEK('zCurrentDayIntervalID',0,'MaxWeekTemp'));

Any help would be greatly appreciated

Jim Diggins

Re: Misbehaving Variables

Gysbert_Wassenaar — Mon, 26 Mar 2018 16:56:09 GMT

How does the variable vMaxCalendarID get its value? That determines the content of the CALTABLE table and what the value of zCurrentWeekIntervalFiscalID in the first row of the CALTABLE is.

Re: Misbehaving Variables

countwise — Tue, 27 Mar 2018 09:25:40 GMT

Hi Gysbert,

See below

Thanks

Jim

-----------------------------------------------------------------------

LET vMinCalendarID = '00000000000000';

LET vMaxCalendarID = '00000000000000';

/// Find Min and Max

MinMaxTemp:

LOAD

MIN(CalendarID) as MinCalendarID,

MAX(CalendarID) as MaxCalendarID

Resident FACT_iCounts;

LET vMinCalendarID = NUM(PEEK('MinCalendarID',0,'MinMaxTemp'));

LET vMaxCalendarID = NUM(PEEK('MaxCalendarID',0,'MinMaxTemp'));

Re: Misbehaving Variables

Gysbert_Wassenaar — Tue, 27 Mar 2018 10:04:09 GMT

And does the maximum calenderid value actually exist in the MaxWeekTemp table?

Re: Misbehaving Variables

countwise — Tue, 27 Mar 2018 11:09:36 GMT

It Does

topic Re: Misbehaving Variables in QlikView

Misbehaving Variables

Re: Misbehaving Variables

Re: Misbehaving Variables

Re: Misbehaving Variables

Re: Misbehaving Variables