How to create a field based fiscal calendar script

[abc_18]

Hi,

I have a date field in my script, how to create a fisal calendar based on that.

I have gone through calendar scripts in qlikcommunity, but most of them are using fiscal month field, I don't have that field available in script, just have date field.

Plesae suggest based on date field how can I create a fiscal calendar.

Thanks

[pradosh_thakur]

Use the below script.

https://community.qlik.com/t5/New-to-QlikView/Fiscal-calendar/m-p/613708

https://community.qlik.com/t5/QlikView-Documents/Fiscal-and-Standard-Calendar-generation/ta-p/148082...

It may seems like fiscal month is used here and not date but that's not the case. You can just change the date field name to your date field and it shall work, You may need to define from which month your fiscal year starts.

-Pradosh

[vikasmahajan]

Create a calendar in your script using following script:

TEMP:
LOAD Max([Posting Date]) AS MaxDate
Table Name;

LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

TEMP1:
LOAD Min([Posting Date]) AS MinDate
Table Name;

LET varMinDate = Num(peek('MinDate', 0, 'TEMP1'));

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS [Posting Date],
TempDate,
TempDate AS %Date,
YearName(TempDate,0,4) AS FinancialYear,
Date(Yearstart(TempDate,0,4)) AS YearStart,
Date(Yearend(TempDate,0,4)) AS YearEnd,
Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
Month(TempDate) AS MonthName,
If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,
MonthStart(TempDate) AS MonthStart,
MonthEnd(TempDate) AS MonthEnd,
Year(TempDate) AS Year,
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
quarterstart(TempDate,0,4) AS QuarterStart,
quarterend(TempDate,0,4) AS QuarterEnd,
quartername(TempDate,0,4) AS QuarterName,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,
AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
NUM(TempDate) AS DateNum,
monthname(TempDate) AS CalMonthYear,
Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,
num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection
num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection
num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS,
Day(Monthend(TempDate)) AS NumberOfDaysInMonth,
ceil(QuarterEnd(TempDate)-QuarterStart(TempDate)) AS NumberOfDaysInQtr,
Date(Yearend(TempDate,0,4)) - Date(Yearstart(TempDate,0,4)) as NumberOfDaysInYr,
if( YearName(TempDate,0,4)='2017-2018',1,0) as YearFlag

RESIDENT TempCalendar
ORDER BY TempDate Asc;

DROP Table TempCalendar;
DROP Table TEMP;
DROP Table TEMP1;

[abc_18]

Thanks for the script, I have added in my application and working fine.

But for quarter filter, I have to show values like below in drop down:-
what should be the script for this, data should come till 2022
Q1 FY19
Q2 FY19
Q3 FY19
Q4 FY19
Q1 FY20
.....
...
Q4 FY22

[abc_18]

Below script I am using in my script:-

data should come till 2022 like below format
Q1 FY19
Q2 FY19
Q3 FY19
Q4 FY19
Q1 FY20
.....
...
Q4 FY22

SET vFiscalYearStartMonth = 10;

LET vStartDate = Num(YearStart(Today(), -1));//will give 1/1/2018

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

'FY' & Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;