Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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;
@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:
@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:
Dear Mr Taoufiq,
Many thanks this indeed works. Oddly this script was the orginal script from qliksense itself.
Best regards,
Mirko