topic Re: Fiscal year? in QlikView

Fiscal year?

Fri, 28 Dec 2012 08:17:48 GMT

Please explain Fiscal year? and calendar year?

Re: Fiscal year?

Fri, 28 Dec 2012 10:17:04 GMT

Definition of a Calendar Year:-

The Internal Revenue Service (IRS) defines a calendar year as 12 consecutive months that begin January 1 and end December 31.

Definition of a Fiscal Year:-

The IRS defines a fiscal year as 12 consecutive months that end on the last day of any month except December or one "that varies from 52 to 53 weeks but does not have to end on the last day of a month."

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

The New England Patroits may elect to operate on a fiscal year , beginning March 1 and ending Feb 28. The reason is, come Dec 31, their year us far from over. There are still playoffs, and a super bowl. This all means income and expenses, and it may make a lot more sense to have all the income and expenses from one season placed into one accounting year.

A calender year system may make sense for a lot of companies, but for others, it might not be the best idea. You cannot change, from one to the other, unless there are compelling reasons.

Most governments opertate on a FY ( fiscal year) starting July 1, because it may take several months after 12/31 to receive and account for all the tax money that is due them.

you can try it.

LOAD *,

Day(date) as day,

Month(date) as month,

Year(date) as year,

yearname(date,0,4)as FinancialYear;

hope it helps you.

Re: Fiscal year?

neha_shirsath — Fri, 28 Dec 2012 10:59:37 GMT

Hii,

Please See this it may help you.

According to Calendar - year start from 1st Jan & year end on 31st Dec means

Quarter, Month, MonthName

Q1,1,Jan

Q1,2,Feb

Q1,3,Mar

Q2,4,Apr

Q2,5,May

Q2,6,Jun

Q3,7,Jul

Q3,8,Aug

Q3,9,Sep

Q4,10,Oct

Q4,11,Nov

Q4,12,Dec

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

But By Fiscal Year it is -

Quarter, Month, MonthName

Q1, 4, Apr

Q1, 5, May

Q1, 6, Jun

Q2, 7, Jul

Q2, 8, Aug

Q2, 9, Sep

Q3, 10, Oct

Q3, 11, Nov

Q3, 12, Dec

Q4, 1, Jan

Q4, 2, Feb

Q4, 3, Mar

Re: Fiscal year?

Fri, 28 Dec 2012 11:06:17 GMT

this code helps for make fiscal year.

LOAD *,

Day(date) as day,

Month(date) as month,

Year(date) as year,

yearname(date,0,4)as FinancialYear;

for example

fiscal year start from 1 april 2012 to 31 march 2013

and calender year start from 1 jan2012 to 31 dec 2012.

hope it helps you.

Re: Fiscal year?

nirav_bhimani — Fri, 28 Dec 2012 11:14:47 GMT

Hi,

Fiscal year start from either March to Feb Or July to june.Its depend on company to company.

Code for Fiscal Year:

MasterCalendar:
LOAD
Date(CalendarDate, 'MM/DD/YYYY') AS CalendarDate,

Day(CalendarDate) AS CalendarDayOfMonth,
WeekDay(CalendarDate) AS CalendarDay,
Week(CalendarDate) AS CalendarWeek,
Month(CalendarDate) AS CalendarMonth,
'Q' & Ceil(Month(CalendarDate)/3) AS CalendarQuarter,

Year(CalendarDate) AS CalendarYear,

//Code for fiscal Year

if(num(month(CalendarDate))<=3,Year(CalendarDate)-1&'-'&right(Year(CalendarDate),2),Year(CalendarDate)&'-'&right(year(CalendarDate)+1,2)) as FinancialYear,
if(num(month(CalendarDate))<=3,Year(CalendarDate),year(CalendarDate)+1) as FinancialYear1,
if(num(month(CalendarDate))>=1 and num(month(CalendarDate))<=3,'Q4',
if(num(month(CalendarDate))>=4 and num(month(CalendarDate))<=6,'Q1',
if(num(month(CalendarDate))>=7 and num(month(CalendarDate))<=9,'Q2',
if(num(month(CalendarDate))>=10 and num(month(CalendarDate))<=12,'Q3')))) as FinancialQuarter

Resident TableName;

Regards,

Nirav Bhimani

Re: Fiscal year?

Tue, 08 Jan 2013 14:47:58 GMT

Hi , This is just what I need! Can you amend the script so the fiscal year runs from Apr-Mar?

Many thanks for your help,

Fiorano

Re: Fiscal year?

Tue, 08 Jan 2013 20:44:24 GMT

I personally created an XLS (month and day only) that maps each calendar date to a fiscal date (QTR and Year) for our company. It worked well.

Re: Fiscal year?

nirav_bhimani — Wed, 09 Jan 2013 06:08:58 GMT

Hi Fiorano,

What kind of changes you need? Its working if you put ur date filed in place of calendar.

In the above script (Financial year consider as Apr-Mar)

-Financial Year show the data like 2011-12.

-FinancialYear1, which use for the set analysis purpose which show the fiscal year of 2011-12 as 2012.

Regards,

Nirav Bhimani

Re: Fiscal year?

Wed, 09 Jan 2013 15:27:34 GMT

Thanks - all fine now!

Re: Fiscal year?

Anonymous — Tue, 05 Feb 2013 02:56:53 GMT

Nirav,

The second part of your code could be simplified as such

year(CalendarDate) as CalendarYear,

year(YearName(CalendarDate, 1, 7)) as FiscalYear, //if fiscal year starts on July 1

'Q' & ceil(month(MonthName(CalendarDate, 6)) / 3) as FiscalQuarter //move 6 months ahead