Qlik Community

QlikView Documents

Documents for QlikView related information.

Fiscal and Standard Calendar generation

MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(Min(billdate)) AS MinDate,

Date(Max(billdate)) AS MaxDate,

FROM BillMaster;

LET vStartDate = Date(Peek(MinDate));

LET vEndDate = Date(Peek(MaxDate));

DROP TABLE DateRange;

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(billdate) AS Year, // Standard Calendar Year

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

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

Regards,

Jagan.

0 Likes
nikhilgarg
Valued Contributor

Hey,

I didn't get some of your statements in above script:

1)  What does  Dual('Q' & Ceil(FiscalMonth/3) return ?? And is value for FiscalMonth = 12 ??

2) What does   Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth)  return ?? and int he function MonthEnd(Date)  does Date is the current date ??

3) What does  Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date returns ?? and What is the value of 'RowNum' ??

Can you explain please ??

Thanks

0 Likes
easternfish
New Contributor III

Hi,

this is the error that I'm getting.

I made some minor changes

DateRange:

LOAD

Date(Min(mydate)) AS MinDate,

Date(Max(mydate)) AS MaxDate

Resident Orders;

any ideas why I'm not able to use this script?

thank you!

0 Likes
MVP & Luminary
MVP & Luminary

Hi,

your variables is not getting the values, it is getting null values.

Regards,

Jagan.

0 Likes
easternfish
New Contributor III

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(Min(mydate)) AS MinDate,

Date(Max(mydate)) AS MaxDate;

//Resident Orders;

SQL SELECT *

FROM Orders;

LET vStartDate = Date(Peek(MinDate));

LET vEndDate = Date(Peek(MaxDate));

DROP TABLE DateRange;

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(mydate) AS Year, // Standard Calendar Year

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

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

yes, I see the vFiscalYearStartMonth but the vStartDate or vEndDate.

what is causing the start or end date not to be created?


Also I got this message after the adding the vStartdate manually

LET vStartDate = 20140401;//Date(Peek(MinDate));

LET vEndDate = 20141231;//Date(Peek(MaxDate));

0 Likes
MVP & Luminary
MVP & Luminary

Hi,

Try like this

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(minDate) AS MinDate,

Date(maxDate) AS MaxDate;

SQL SELECT

Min(mydate) AS minDate,

Max(mydate) AS maxDate

FROM Orders;

Regards,

Jagan.

0 Likes
easternfish
New Contributor III

i'm getting a ....

Field not found - <minDate>

SQL SELECT

Min(mydate) AS minDate,

Max(mydate) AS maxDate

FROM Orders;

here's how I have the script

--------------

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(minDate) AS MinDate,

Date(maxDate) AS MaxDate;

SQL SELECT

Min(mydate) AS minDate,

Max(mydate) AS maxDate

FROM Orders;

LET vStartDate = Date(Peek(MinDate));

LET vEndDate = Date(Peek(MaxDate));

DROP TABLE DateRange;

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(mydate) AS Year, // Standard Calendar Year

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

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

thanks

0 Likes
MVP & Luminary
MVP & Luminary

Hi,

The script definitely works?  I think may be the casing issue.Can you attach the sample data?

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(MINDATE) AS MinDate,

Date(MAXDATE) AS MaxDate;

SELECT

Min(mydate) AS MINDATE,

Max(mydate) AS MAXDATE

FROM Orders;

Regards,

Jagan.

0 Likes
easternfish
New Contributor III

I'm sure that I'm missing a step. let me ask you. out of the original script ( Dec 8, 2014 11:46 PM (in response to suresh babu) )   what table/ data fields /names do i need to change in order to make it work with my database?

thank you again for all your help!

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD

Date(Min(billdate)) AS MinDate,

Date(Max(billdate)) AS MaxDate,

FROM BillMaster;

LET vStartDate = Date(Peek(MinDate));

LET vEndDate = Date(Peek(MaxDate));

DROP TABLE DateRange;

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(billdate) AS Year, // Standard Calendar Year

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

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

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

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

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

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

0 Likes
easternfish
New Contributor III

It looks like I got it working by just adding the following to the Tab.....

SET vFiscalYearStartMonth = 4;

YearName((date(date#(mydate, 'YYYYMMDD'),'YYYY/MM/DD')), 0, $(vFiscalYearStartMonth)) AS Year,  // Fiscal Calendar Year

Mod(Month(date(date#(mydate, 'YYYYMMDD'),'YYYY/MM/DD')) - $(vFiscalYearStartMonth), 12)+1 AS Month,  // Fiscal Calendar Month

but I'm still testing.

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