Qlik Community

QlikView Documents

Documents for QlikView related information.

Fiscal and Standard Calendar generation

MVP
MVP

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
cbaqirdhds
Contributor II

This is great, thank you. Question, How can I get the Fiscal Year list box to show the years relevant to my other data?

MVP
MVP

Hi,

Check this script

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($(MinDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

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

AutoGenerate MaxDate- MinDate+ 1;

Load

    Min(FactDate) AS MinDate,

    Max(FactDate) AS MaxDate

RESIDENT FactTableName;

Note: Replace FactTableName with your actual fact table name and FactDate with your actual date field name.

Regards,

Jagan.

cbaqirdhds
Contributor II

Isn't that the same script as above?

MVP
MVP

Hi,

Almost same, but in this we are getting min and max from fact table and based on that we are generating Master Calendar table.  In the document script we are generating the predefined date ranges.

Regards,

Jagan.

Not applicable

Hi Jagan,

In the above script what is the fact table name ?

MVP
MVP

Hi,

I didn't included that table script, you replace with your table name.

Regards,

Jagan.

cbaqirdhds
Contributor II

I am getting this error:

Script line error:
LOAD

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

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

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

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


AutoGenerate MaxDate- MinDate+ 1

and also:

Field not found - <Timesheet Start Date>
Load

    Min([Timesheet Start Date]) AS MinDate,

    Max([Timesheet Start Date]) AS MaxDate

RESIDENT [PPM Time by Task]

MVP
MVP

Hi,

Is this field Timesheet Start Date exists in your table [PPM Time by Task]?

Regards,

Jagan.

cbaqirdhds
Contributor II

Yes.

sakamsureshbabu
Contributor

hi mohan

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;

so my table name 'Billmaster'

and date filed name 'billdate' now how can i use this script for Fiscalyear and normal year...

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