Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY

Fiscal and Standard Calendar generation

jagan
Luminary Alumni
Luminary Alumni

Fiscal and Standard Calendar generation

Hi All,

What is Fiscal Calendar?

From Wikipedia:

A fiscal year (or financial year, or sometimes budget year) is a period used for calculating annual ("yearly") financial statements in businesses and other organizations.

Fiscal years vary between businesses and countries. The "fiscal year" may also refer to the year used for income tax reporting.

In India, the government's financial year runs from 1 April to 31 March midnight.

Example: 1 April 2014 to 31 March 2015 for the financial year 2014–2015. It is also abbreviated as FY15.

The United States federal government's fiscal year is the 12-month period ending on 30 September of that year, having begun on 1 October of the previous calendar year. In particular, the identification of a fiscal year is the calendar year in which it ends; thus, the current fiscal year is 2014, often written as "FY2014" or "FY14", which began on 1 October 2013 and which will end on 30 September 2014.

Below script helps in generating the Standard and Fiscal Year Calendar generation, for this you need to configure 3 variables,

vFiscalYearStartMonth - Tells the starting month of the Fiscal Year

vStartDate - Starting date of the Calendar generation

vEndDate - Ending date of the Calendar generation

Script:

----------

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

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

FiscalCalendar:

LOAD

*,

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;

Note: Based on your requirement you can modify the script to arrive new fields like Week, YTD, MTD etc.

Regards,

Jagan.



Comments
jagan
Luminary Alumni
Luminary Alumni

Hi Anson,

Check this link

Fiscal Calendar with Non-Standard Days (Not 1-31)

Regards,

Jagan.

Not applicable

Thank you Jagan,

I can finish my task, I think the tip is using Function monthstart

Here is my code, Thank you again

//*********************Create Period****************

MinMax:

Load

  Min(TransDate) as MinDate, //get the first date from existing table

  Max (TransDate) as MaxDate //get the last date from existing table

Resident Link;

**************************************************************************************************

Let vMinDate=num(peek('MinDate',0,'MinMax'));

Let vMaxDate=num(peek('MaxDate',0,'MinMax'));

//********************Create Temp Calendar*********************

TemCal:

LOAD 

//Date(makedate(year($(vMinDate)))+recno()-1) as Date

Date($(vMinDate)+RowNo()-1) as Date 

AutoGenerate $(vMaxDate)-$(vMinDate)+1

;

drop Table MinMax;

 

//************************FiscalCalendar:******************************** 

Period:

LOAD

Date as TransDate,

Date(monthstart(Date-28,1)) as FiscalDate, 

Day(Date(Date-28)) AS DayNumberInMonth,

Date(monthstart(Date-28,1),'YYYY-MM') as Period

Resident TemCal

Order By Date ASC;

drop table TemCal;

0 Likes
manoj217
Creator III
Creator III

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

//Create min and max date (max=current date for the assignment)

Temp:

//load date(minDate,'YYYY-MM-DD') AS minDate,  date(maxDate,'YYYY-MM-DD') AS maxDate inline [

//minDate, maxDate

//'2005-12-30', '2009-06-30'

//];

load min([Order Date]) as minDate,

    max([Order Date]) as maxDate

    Resident sales;

SET vFiscalYearStartMonth = 7;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

MasterCalendar: 

LOAD

*,

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

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

Load 

//Master Calendar will Join its fields with Key_OrderDate field From our DB

               TempDate AS Key_OrderDate, 

               week(TempDate) As OrderWeek, 

               Year(TempDate) As OrderYear, 

               Month(TempDate) As OrderMonth, 

               Day(TempDate) As OrderDay, 

               YeartoDate(TempDate)*-1 as OrderCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as OrderLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as OrderRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as OrderMonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as OrderQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as OrderWeekYear, 

               WeekDay(TempDate) as OrderWeekDa,

              

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

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

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

0 Likes
haribabugv
Creator
Creator

Hi

This is such an awesome script

I tried replicating your fiscal calendar script in my test code.

i am getting an error saying Autogenerate out of range

can you please check below errors?

jagan

Autogenerate.JPG

Autogenerate_1.JPG

0 Likes
Anismohamed32
Partner
Partner

Anis + Qlik

 

Let vDateMin = $(vAppStartDate);
Let vDateMax = Num(Floor(YearEnd(Today(),0,4)));

 

Let vFiscalStartMonth = 4;
Let vToday=Date(Floor(Today()),'MM/DD/YYYY');
Let vCurrentYear = num(year(today()));
Let vShowFunds=0;

[Master Calendar]:
LOAD *,
Year & [Month (#)] as [Period (#)],
AutoNumber(Year & [Month (#)], 'MonthID') as MonthID,
Month & ' - ' & Year as Period,
'Q' & Ceil([Month (#)] / 3) as Quarter,
ceil(num(Interval(WeekEnd(Date)-MonthStart(Date)))/7) as MonthlyWeekNumber,
AutoNumber(Year & 'Q' & Ceil([Month (#)] / 3), 'QuarterID') as QuarterID,
WeekDay( Date, $(FirstWeekDay) ) as WeekDay,
WeekStart( Date, 0, $(FirstWeekDay) ) as WeekStart;

LOAD
Date($(vDateMin) + IterNo() - 1, '$(DateFormat)') as Date,
Date($(vDateMin) + IterNo() - 1, '$(DateFormat)') as DocDate,
Day($(vDateMin) + IterNo() - 1) as [Day (#)],
Day($(vDateMin) + IterNo() - 1) as DayNum,
// WeekDay($(vDateMin) + IterNo() - 1) as WeekDay,
Week($(vDateMin) + IterNo() - 1) as Week#,
Year($(vDateMin) + IterNo() - 1) as Year,
Date(yearstart(($(vDateMin) + IterNo() - 1),0,$(vFiscalStartMonth)),'YYYY') as FiscalYear,
'Q'&Ceil(WildMatch(Month(($(vDateMin) + IterNo() - 1)),'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar')/3) as FiscalQuarter,
Dual(Month(($(vDateMin) + IterNo() - 1)), Mod((Month(($(vDateMin) + IterNo() - 1))-$(vFiscalStartMonth)),12)+1) as FiscalMonth,
Dual(Month(($(vDateMin) + IterNo() - 1)), Mod((Month(($(vDateMin) + IterNo() - 1))-$(vFiscalStartMonth)),12)+1) as FiscalMonth1,
Date(monthstart(($(vDateMin) + IterNo() - 1)),'MMM YYYY') as "MonthYr",
Month($(vDateMin) + IterNo() - 1) as Month,
Num(Month($(vDateMin) + IterNo() - 1), '00') as [Month (#)]
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-09-01 10:30 PM
Updated by: