topic Re: How to create a field based fiscal calendar script in App Development

How to create a field based fiscal calendar script

abc_18 — Thu, 28 Mar 2019 05:45:48 GMT

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

Re: How to create a field based fiscal calendar script

pradosh_thakur — Thu, 28 Mar 2019 07:31:08 GMT

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/1480822

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

Re: How to create a field based fiscal calendar script

vikasmahajan — Thu, 28 Mar 2019 10:09:46 GMT

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;

Re: How to create a field based fiscal calendar script

abc_18 — Thu, 28 Mar 2019 12:46:42 GMT

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

Re: How to create a field based fiscal calendar script

abc_18 — Thu, 28 Mar 2019 17:04:41 GMT

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;