Script issue setting variable: Server update vs manual update

slamster44 — Fri, 07 Dec 2018 22:33:15 GMT

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;

Re: Script issue setting variable: Server update vs manual update

Anil_Babu_Samineni — Sat, 08 Dec 2018 00:26:46 GMT

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

Re: Script issue setting variable: Server update vs manual update

slamster44 — Sat, 08 Dec 2018 04:14:08 GMT

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

Re: Script issue setting variable: Server update vs manual update

PrashantSangle — Sat, 08 Dec 2018 11:14:07 GMT

In which variable you are facing issue???

Regards,

Re: Script issue setting variable: Server update vs manual update

slamster44 — Fri, 14 Dec 2018 20:38:43 GMT

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.

Re: Script issue setting variable: Server update vs manual update

PrashantSangle — Mon, 17 Dec 2018 03:52:49 GMT

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;

Re: Script issue setting variable: Server update vs manual update

slamster44 — Tue, 18 Dec 2018 20:49:29 GMT

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.

topic Re: Script issue setting variable: Server update vs manual update in QlikView

Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update

Re: Script issue setting variable: Server update vs manual update