Different fiscal year from calender year

[#1]

Dear all,

I am loading excel files with data containing a date (dd.mm.yyyy). from January until december. I use month() and year() to seperate the parts of the date and create listboxes to display the values.

But then I want to calculate the sum(value) not of the calender year which is QV using but I want to calculate sums from May to April, a different fiscal year.

All my formulars are using the calender year but I want to "force" them to uses a different time setting.

Is there a possible was to use a calender from May to April, e.g. May 2010 - April 2011, May 2001 - April 2012 and so on.

Thanks in advance

Thomas

[#2 · Accepted solution]

Yes this is possible by "shifting" dates in your date dimension. Below is an example of a calendar i recently implemented where the fiscal year starts the first of september. This is done by "shifting" the dates 4 months forward. (in other words the first of september is seen as the first day of the first month of a fiscal year).

In your situation you either have to shift time backwards by 4 months or forward by 8 (this depends on how you want to call your fiscal year).

Here is the calendar you can use as a reference:

LET vDateMin = Num(MakeDate(2008,9,1));
LET vDateMax = Floor(AddMonths(YearEnd(AddMonths(Today(),4)),-4));

CalendarDates:
LOAD $(vDateMin) + RowNo() - 1 AS DateNumber
, Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:
LOAD TempDate AS %Key_Date
, TempDate AS Date
//, Day(TempDate) AS Day
//, WeekDay(TempDate) AS WeekDay
, Num(Week(TempDate),00) AS Week
, Month(TempDate) AS Month
, Num(Month(TempDate),00) AS MonthNum
, Year(TempDate) AS Year
, 'Q' & Ceil(Month(TempDate)/3) AS Quarter
, WeekYear(TempDate) & '-w' & Num(Week(TempDate),00) AS YearWeek
, Year(TempDate) & '-' & Num(Month(TempDate),00) AS YearMonth
, Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3) AS YearQuarter
, Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum
, 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) AS FiscalYear
, 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) & ' Q' & Ceil(Month(AddMonths(TempDate,4))/3) AS FiscalYearQuarter
RESIDENT CalendarDates
ORDER BY TempDate ASC;

DROP TABLE CalendarDates;

[spividori]

HI.

In the script you could set Month-Year field as follows:

Month(Date)&'-'&year(Date) as MonthYear

and use it to select the period.

Hope this helps.

Regards.

[#4]

Hi Toine Rozemeijer, I wil ltry it and see hiw it works. Thanks.

[#5]

Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum

, 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) AS FiscalYear

, 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) & ' Q' & Ceil(Month(AddMonths(TempDate,4))/3) AS FiscalYearQuarter

I was searching around and was looking to add a Fiscal Calendar to my Master.  I liked what you had here but made a slight change. In the line " Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum".  I changed the 4 to a 3. It appeared that it was not labeling the months correctly to the Fiscal Quarter

thank you

rb