Script issue setting variable: Server update vs manual update

[slamster44]

My client's calendar script sets the fiscal year as a variable.  Every year November, it calculates the current fiscal year from the invoice date and sets the variable.  We have Qlikview Business Server and it updates the application daily.

Last year when the Fiscal year changed from 2017-2018, the application updated the variable correctly - overnight server update

This year, when running the server update process the variable does not set.  We ran the server update on the schedule and manually and the variable stayed the same.

We then loaded the application and ran a "manual" update and the variable changed to the current fiscal year.

The problem is why did the variable update one year when the server ran the update but this year it took a manual update to trigger the change?

Calendar SCRIPT - Fiscal year section in blue

///$tab Calendar
SET vDefBegFisYr=2007;

TEMP:
LOAD
num(min(Date_InvoiceDate)) AS MinDate,
num(max(Date_InvoiceDate)) AS MaxDate,
num(max(Date_ProcDate)) AS ProcessDate
RESIDENT
FACT_OrderHeader;

LET vMinDate = peek('MinDate', 0, 'TEMP');
LET vMaxDate = peek('MaxDate', 0, 'TEMP');
LET iDateBeg = peek('MinDate', 0, 'TEMP');
LET iDateEnd = peek('MaxDate', 0, 'TEMP');
LET vProcessDate = peek('ProcessDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIsland:
LOAD
date($(vMinDate) + rowno() - 1) AS D,
year($(vMinDate) + rowno() - 1) AS Y,
month($(vMinDate) + rowno() - 1) AS M,
date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE
vMaxDate - vMinDate + 1;

LET vInvDate =$(vMaxDate);

LET vToday = $(vProcessDate);
LET vDate_Today=Date($(vToday));
LET vSelectedDay = $(vProcessDate);
LET vDate_Selected=Date($(vSelectedDay));

// FISCAL Month/Year
LET vCurFisMo=num(month(AddMonths(MonthsName(1,$(vInvDate),0,11),-10)),'#0');
LET vCurFisYr=num(Year(AddMonths(MonthsName(1,$(vInvDate),0,11),+2)),'#0');
LET vLastFisMo=$(vCurFisMo)-1;
LET vLastFisYr=$(vCurFisYr)-1;

//num(month($(vInvDate)));

LET vThisYear = year($(vInvDate));
LET vLastYear = year($(vInvDate)) - 1;

LET vThisMonthText = month($(vInvDate));
LET vLastMonth = num(month(addmonths($(vInvDate), -1)));
LET vLastMonthText = month(addmonths($(vInvDate), -1));
LET vTodayWithinYear = num($(vInvDate) - yearstart($(vInvDate))) + 1;
LET vTodayWithinMonth = num($(vInvDate) - monthstart($(vInvDate))) + 1;

//LET vPriorYearDate = '=date(addyears(max(Date_ProcDate),-1),' & chr(39) & 'M/DD/YYYY' & chr(39) & ')';
LET vPriorYearDate = AddYears($(vProcessDate),-1);

Calendar:
LOAD
D AS Link_Date,
D AS Date,
D AS Link_Daily, //links Daily Sales Reports
D AS LINK_WA_Date, //links Warehouse Activity
// D AS LINK_AP_Date, //links AP Activity
// D AS LINK_AR_Date, //links AR Activity
// D AS LINK_INV_Date,//links On Hand INV Calendar

// Basic Date Dimensions
Y AS Year,
M AS Month,
MY AS MonthYear,
num(MY) AS NumericMonthYear,
'Q' & ceil(M / 3) AS Quarter,
week(D) AS Week,
weekday(D) AS Weekday,
day(D) AS Day,

//Fiscal Dates
'Q' & Ceil(month(AddMonths(MonthsName(3,D,0,11),-10))/3) as FisQuarter,
num(month(AddMonths(MonthsName(1,D,0,11),-10)),'#0') as FisMo,
num(Year(AddMonths(MonthsName(1,D,0,11),+2)),'#0') as FisYr,
month(D) as FisMoShort,

// Flags for determining time periods
inyear(D, $(vInvDate), 0) * -1 AS CAL_CurrentYear, //Current year
inyeartodate(D, $(vInvDate), 0) * -1 AS CAL_CurrentYTD, //Current year-to-date
inquarter(D, $(vInvDate), 0) * -1 AS CAL_CurrentQtr, //Current qua
inmonth(D, $(vInvDate), 0) * -1 AS CAL_CurrentMonth, //Current month
inmonth(D, $(vInvDate), -1) * -1 AS CAL_LaStMonth, //Previous month
inmonthtodate(D, $(vInvDate), 0) * -1 AS CAL_CurrentMTD, //Current month-to-date
inmonthtodate(D, $(vInvDate), -1) * -1 AS CAL_LastMonthMTD, //Previous month-to-date
inday(D, $(vInvDate), 0) * -1 AS CAL_CurrentDay,
inweek(D, $(vToday), 0) * -1 AS CAL_CurrentWeek, //Current day
inweek(D, $(vInvDate), -1) * -1 AS CAL_LastWeek, //One week ago
inweektodate(D, $(vInvDate), 0) * -1 AS CAL_CurrentWTD, //Current week-to-date
inweektodate(D, $(vInvDate), -1) * -1 AS CAL_LastWTD //One week ago to date
RESIDENT
DateIsland;

[Anil_Babu_Samineni]

Why Num(Expression, '#0') required? Instead can't you use simply Num(Expression, 00)

[slamster44]

Honestly - I adapted the calendar script from the community so not sure how to answer that - Does it make a difference to my issue?

[PrashantSangle]

In which variable you are facing issue???

Regards,

[slamster44]

LET vCurFisYr=num(Year(AddMonths(MonthsName(1,$(vInvDate),0,11),+2)),'#0'); 

last year, it updated fine from Fiscal year 2017 to 2018,

This year we had to run the script manually for it to update.

[PrashantSangle]

Hi,

What is output maxdate of in below script? and value of
LET vMaxDate = peek('MaxDate', 0, 'TEMP');


TEMP:
LOAD
num(min(Date_InvoiceDate)) AS MinDate,
num(max(Date_InvoiceDate)) AS MaxDate,
num(max(Date_ProcDate)) AS ProcessDate
RESIDENT
FACT_OrderHeader;

[slamster44]

it is either the numerical date which i then convert using DATE(vMaxDate)

or it is the actual Max date of the invoice.  

When it sees 11/6/2018 as the vMaxDate it should recognize it is the new fiscal year and set vCurFisYr to 2019.

Last year it rolled over fine, this year it did not until we manually ran the script.  I am thinking this is more a bug issue versus a variable setting issue. 

Unfortunately, right now I do not have access to the application for testing.