Qlik Community

Search the Community

cancel
Showing results for 
Search instead for 
Did you mean: 
MirkO1
Contributor
Contributor

Fiscal year script

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;

1 Solution

Accepted Solutions
Taoufiq_Zarra

@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:

Taoufiq_Zarra_0-1642016222012.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

@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:

Taoufiq_Zarra_0-1642016222012.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
MirkO1
Contributor
Contributor
Author

Dear Mr Taoufiq,

 

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

 

Best regards,

 

Mirko

Community Browser