Fiscal year script

MirkO1 — Wed, 12 Jan 2022 19:06:56 GMT

Dear,

I am running the script as provided in the exercise but it generates an error , i did not change it nor do I see a mistake in the script can. What am I missing?

error:

Started loading data

Orders << Orders Lines fetched: 13,142

The following error occurred:

Unexpected token: ')', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

The error occurred here:

LET vStartDate = Num(YearStart(, -1)>>>>>>)<<<<<<

Data has not been loaded. Please correct the error and try loading again.

script from exercise provided by qlik

// loading Orders data
Orders:
LOAD
OrderRecordCounter,
OrderID,
Date(OrderDate) AS OrderDate,
CustomerID,
EmployeeID,
ShipperID,
FreightWeight,
OrderStatus,
LastUpdated
FROM [lib://DataFiles/Orders.qvd]
(qvd);

//vFiscalYearStartMonth - Tells the starting month of the Fiscal Year
//vStartDate - Starting date of the Calendar generation
//vEndDate - Ending date of the Calendar generation

LET vToday = Num('20-Mar-2015');

SET vFiscalYearStartMonth = 7;
LET vStartDate = Num(YearStart($(vToday), -1));
LET vEndDate = Num(YearEnd($(vToday)));

FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(OrderDate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD
*,
Year(OrderDate)
as Year, // Standard Calendar Year
Month(OrderDate)
as Month, // Standard Calendar Month
Date(MonthEnd(OrderDate), 'MMM')
as MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(OrderDate)/3), Ceil(Month(OrderDate)/3))
as Quarter, // Standard Calendar Quarter
Mod(Month(OrderDate) - $(vFiscalYearStartMonth), 12) + 1
as FiscalMonth, // Fiscal Calendar Month
YearName(OrderDate, 0, $(vFiscalYearStartMonth))
as FiscalYear; // Fiscal Calendar Year

LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1, 'DD-MMM-YYYY')
as OrderDate,
RangeSum(Peek('RowNum'), 1)
as RowNum
AutoGenerate vEndDate - vStartDate + 1;

Re: Fiscal year script

Taoufiq_Zarra — Wed, 12 Jan 2022 19:37:16 GMT

@MirkO1 if I understood correctly

can you check your date format in LET vToday = Num('20-Mar-2015');

for example if your date default format is :

SET DateFormat='DD/MM/YYYY';

you can use :

LET vToday = floor(Num('20/04/2015'));

SET vFiscalYearStartMonth = 7;
LET vStartDate = floor(Num(YearStart($(vToday), -1)));
LET vEndDate = floor(Num(YearEnd($(vToday))));

debeug output:

Re: Fiscal year script

MirkO1 — Thu, 13 Jan 2022 08:40:02 GMT

Dear Mr Taoufiq,

Many thanks this indeed works. Oddly this script was the orginal script from qliksense itself.

Best regards,

Mirko

topic Fiscal year script in Search the Community

Fiscal year script

Re: Fiscal year script

Re: Fiscal year script