Fiscal YTD Filter

Jody · Nov 13, 2008

I have looked through the threads on Fiscal Year information, and agree that
fiscal information is a pain to collect, not to mention to compare it
dynamically to the previous fiscal year. I apologize in advance for the
length of this post.

Using Office 2007, here is my problem: I need to pull Fiscal Year-to-date
(YTD) information for defined fiscal periods. Our fiscal year begins in
October.

To give you an explaination as to where I am at currently, in my query, I
have fiscal year periods defined as

Code:

Period:
IIf([DOCDATE]>#9/30/2008#,2009,IIf([DOCDATE]>#9/30/2007#,2008,IIf([DOCDATE]>#9/30/2006#,2007,IIf([DOCDATE]<#10/1/2006#,2006,2010))))

Additionally, I needed to compare Fiscal Month to Fiscal Month so I came up
with this:

Code:

FiscalMonth:
IIf(Month([docdate])=10,1,IIf(Month([docdate])=11,2,IIf(Month([docdate])=12,3,IIf(Month([docdate])=1,4,IIf(Month([docdate])=2,5,IIf(Month([docdate])=3,6,IIf(Month([docdate])=4,7,IIf(Month([docdate])=5,8,IIf(Month([docdate])=6,9,IIf(Month([docdate])=7,10,IIf(Month([docdate])=8,11,IIf(Month([docdate])=9,12,0))))))))))))

What I now need to be able to do is compare Fiscal YTD (last fiscal year) to
Fiscal YTD (this fiscal year). More specifically, if I am in fiscal month 4
(which would be January 2009 in my case) then I need to compare October thru
January FY 2008 to October thru January FY 2009 without getting the remaining
eight months in FY 2008 pulled into the equation.

It feels like something like this would be what I am after:

Code:

iif(IIf(Month([docdate])=10,1) = 1, >= 10/1 and <= 10/31,
IIF(IIf(Month([docdate])=11,2) = 2, >= 10/1 and <= 11/30)).......
[\Code]

But I am at a loss for the proper syntax or placement of this formula.
Would I just need to put it in the criteria of the [DOCDATE] field?  Would I
need to build a new field called [Fiscal YTD]?  Am I on the wrong path
completely?

It may be worth a mention that I am using this Access query to drive an
Excel Pivot Table.  So, maybe I am on the wrong side of the fence with where
I need to build this filter constraint.

KARL DEWEY · Nov 13, 2008

Fiscal year is easy. Your fiscal year begins in October so that 11 OCT 08 is
day 11 of FY 2009.
Just use this --
FY_Date: DateAdd("m",3,[YourDateField])
This adds 3 months to whatever date you have.

Then you can use it to find ant FY data --
FY: Year(DateAdd("m",3,[YourDateField]))
FY_Mon: Format(DateAdd("m",3,[YourDateField]), "yyyymm")
FY_QTR: Format(DateAdd("m",3,[YourDateField]), "yyyyq")

Ken Sheridan · Nov 13, 2008

The following function will return the accounting year for any date in the
format 2008-2009 by passing in the date in question, and the month and day on
which the accounting year starts. Paste the functi0n into a standard module,
which you should save under a name other than that of the function, e.g.
mdlDateStuff, and you can call it anywhere in the database:

Public Function GetAccountingYear(varDate As Variant, _
intStartMonth As Integer, _
intStartDay As Integer) As String

Dim intYear As Integer
intYear = Year(varDate)

If Not IsNull(varDate) Then
If varDate < DateSerial(intYear, intStartMonth, intStartDay) Then
GetAccountingYear = intYear - 1 & "-" & intYear
Else
GetAccountingYear = intYear & "-" & intYear + 1
End If
End If

End Function

To determine whether a date is in the previous accounting year to date, or
in your case to end of date's month, you can firstly check to see it the date
is in the previous accounting year, and if so whether it is in a month up to
and including the current month. The first is simply a question of comparing
the accounting year for the current ate with the accounting year of the date
in question plus one year; the second can be done by comparing the 1st of the
month dates for the date in question and the current month. You can wrap
this up in another function in the same module, which call the first function:

Public Function PrevAccYearToDate(varDate As Variant, _
intYearComp As Integer, _
intMonthComp As Integer, _
intMonthStart As Integer, _
intDayStart As Integer) As Boolean

' return false by default
PrevAccYearToDate = False

If Not IsNull(varDate) Then
' is date in previous accounting year
If GetAccountingYear(varDate, intMonthStart, intDayStart) = _
GetAccountingYear(DateAdd("yyyy", -1, _
DateSerial(intYearComp, intMonthComp, 1)), _
intMonthStart, intDayStart) Then
' does day fall in or before relevant accounting month
If DateSerial(Year(varDate) + 1, Month(varDate), 1) _
<= DateSerial(intYearComp, intMonthComp, 1) Then
PrevAccYearToDate = True
End If
End If
End If

End Function

This allows you to enter the year and month to compare (2099 and 1 for
Fanuaru 2009 in your example) and the start month and day of the accounting
year (10 and 1 in your case) so in a query you'd call the function with:

WHERE PrevAccYearToDate([DocDate],2009,1,10,1)

To do it query design view paste everything after the WHERE into the 'field'
row of a blank column as a a single line and enter True in the 'criteria'
row. Uncheck the 'show' checkbox.

You could also use parameters to enter the year and month, 2009 and 1 in
your example, at runtime:

WHERE PrevAccYearToDate(DateUpdated,[Year to compare:],[Month to
compare:],10,1)

Or so that it always compares the current year month when the query is
opened you can use the Date() function:

WHERE PrevAccYearToDate(DateUpdated,Year(Date()),Month(Date()),10,1)

BTW the above does assume that the table contains no Null docdates.

Ken Sheridan
Stafford, England

Jody said:
I have looked through the threads on Fiscal Year information, and agree that
fiscal information is a pain to collect, not to mention to compare it
dynamically to the previous fiscal year. I apologize in advance for the
length of this post.

Using Office 2007, here is my problem: I need to pull Fiscal Year-to-date
(YTD) information for defined fiscal periods. Our fiscal year begins in
October.

To give you an explaination as to where I am at currently, in my query, I
have fiscal year periods defined as

Code:

Period: IIf([DOCDATE]>#9/30/2008#,2009,IIf([DOCDATE]>#9/30/2007#,2008,IIf([DOCDATE]>#9/30/2006#,2007,IIf([DOCDATE]<#10/1/2006#,2006,2010))))

Additionally, I needed to compare Fiscal Month to Fiscal Month so I came up
with this:

Code:

FiscalMonth: IIf(Month([docdate])=10,1,IIf(Month([docdate])=11,2,IIf(Month([docdate])=12,3,IIf(Month([docdate])=1,4,IIf(Month([docdate])=2,5,IIf(Month([docdate])=3,6,IIf(Month([docdate])=4,7,IIf(Month([docdate])=5,8,IIf(Month([docdate])=6,9,IIf(Month([docdate])=7,10,IIf(Month([docdate])=8,11,IIf(Month([docdate])=9,12,0))))))))))))

What I now need to be able to do is compare Fiscal YTD (last fiscal year) to
Fiscal YTD (this fiscal year). More specifically, if I am in fiscal month 4
(which would be January 2009 in my case) then I need to compare October thru
January FY 2008 to October thru January FY 2009 without getting the remaining
eight months in FY 2008 pulled into the equation.

It feels like something like this would be what I am after:

Code:

iif(IIf(Month([docdate])=10,1) = 1, >= 10/1 and <= 10/31, IIF(IIf(Month([docdate])=11,2) = 2, >= 10/1 and <= 11/30))....... [\Code] But I am at a loss for the proper syntax or placement of this formula. Would I just need to put it in the criteria of the [DOCDATE] field? Would I need to build a new field called [Fiscal YTD]? Am I on the wrong path completely? It may be worth a mention that I am using this Access query to drive an Excel Pivot Table. So, maybe I am on the wrong side of the fence with where I need to build this filter constraint.[/QUOTE]

Ken Sheridan · Nov 13, 2008

Actually it will handle Nulls, returning False.

Ken Sheridan
Stafford, England

Mark Andrews · Nov 13, 2008

As a general comment when dealing with Fiscal years, months quarters etc....
It might help to have a table populated with fiscal data so that you can
just join this
to any date or do other types of operations. It's a common data warehousing
technique.

table would have
-date
-month
- day
- year
- Fiscal Year
- Fiscal month
- isholiday
-etc...

You should be able to find SQL to build a table populated with 10 years or
whatever you might need.

Then you could join your table by date to this table and just use the
Fiscalyear category to group on to get sums by fiscal year, order things by
fiscal month etc...

HTH,
Mark
RPT Software
http://www.rptsoftware.com

Jody said:
I have looked through the threads on Fiscal Year information, and agree
that
fiscal information is a pain to collect, not to mention to compare it
dynamically to the previous fiscal year. I apologize in advance for the
length of this post.

Using Office 2007, here is my problem: I need to pull Fiscal Year-to-date
(YTD) information for defined fiscal periods. Our fiscal year begins in
October.

To give you an explaination as to where I am at currently, in my query, I
have fiscal year periods defined as

Code:

Period: IIf([DOCDATE]>#9/30/2008#,2009,IIf([DOCDATE]>#9/30/2007#,2008,IIf([DOCDATE]>#9/30/2006#,2007,IIf([DOCDATE]<#10/1/2006#,2006,2010))))

Additionally, I needed to compare Fiscal Month to Fiscal Month so I came
up
with this:

Code:

FiscalMonth: IIf(Month([docdate])=10,1,IIf(Month([docdate])=11,2,IIf(Month([docdate])=12,3,IIf(Month([docdate])=1,4,IIf(Month([docdate])=2,5,IIf(Month([docdate])=3,6,IIf(Month([docdate])=4,7,IIf(Month([docdate])=5,8,IIf(Month([docdate])=6,9,IIf(Month([docdate])=7,10,IIf(Month([docdate])=8,11,IIf(Month([docdate])=9,12,0))))))))))))

What I now need to be able to do is compare Fiscal YTD (last fiscal year)
to
Fiscal YTD (this fiscal year). More specifically, if I am in fiscal month
4
(which would be January 2009 in my case) then I need to compare October
thru
January FY 2008 to October thru January FY 2009 without getting the
remaining
eight months in FY 2008 pulled into the equation.

It feels like something like this would be what I am after:

Code:

iif(IIf(Month([docdate])=10,1) = 1, >= 10/1 and <= 10/31, IIF(IIf(Month([docdate])=11,2) = 2, >= 10/1 and <= 11/30))....... [\Code] But I am at a loss for the proper syntax or placement of this formula. Would I just need to put it in the criteria of the [DOCDATE] field? Would I need to build a new field called [Fiscal YTD]? Am I on the wrong path completely? It may be worth a mention that I am using this Access query to drive an Excel Pivot Table. So, maybe I am on the wrong side of the fence with where I need to build this filter constraint.[/QUOTE]

Jody · Nov 13, 2008

Thank you for your replies.

While all of these are very helpful (as I have attempted each one and tested
what thay do) it's not exactly what I need. I have got the fiscal
functionality on a month to month basis. What I need is where Fiscal Month =
1 then return data for 10/1 through 10/31 (for the periods - i.e. 2007 and
2008 - that I select) OR, if Fiscal Month = 2 then return data for 10/1
through 11/30 for the periods I select.

I need the Fiscal YTD data to only show me values for the previous year up
to the matching month (i.e. if I am requesting data for March, I need only to
compare to the previous year data October through March and not see April
through September for that year).

This is where I am running into trouble. I don't know how to say "show me
this date range for this year and last year" based on what month I am in.

KARL DEWEY · Nov 13, 2008

I need only to compare to the previous year data October through March and
not see April through September for that year).
Your criteria is mixing Fiscal Year and calendar months.
Use what I posted for FY and enter the 4 digit FY for criteria.
Calendar month March is the 6th month of your FY. Then you need a calculated
field for fiscal year-month as I posted.
As criteria on the fiscal year-month field use this criteria --
<=IIF([Enter calendar month number] <= 9, [Enter calendar month number]
+12, [Enter calendar month number]) -9

How this works --
CAL_Mon if<=9 add -- Results Subtract Criteria FY_Mon
10 0 10 -9 1 1
11 0 11 -9 2 2
12 0 12 -9 3 3
1 12 13 -9 4 4
2 12 14 -9 5 5
3 12 15 -9 6 6
4 12 16 -9 7 7
5 12 17 -9 8 8
6 12 18 -9 9 9
7 12 19 -9 10 10
8 12 20 -9 11 11
9 12 21 -9 12 12
If you paste into Excel it will correct the wrap so you can see it better.

Mark Andrews · Nov 14, 2008

Jody,

Have you tried the table concept I suggested? If so then just join to the
table and select
where FiscalMonth = 1 and ((FiscalYear = FY07) or (FiscalYear = FY08))

for the first 2 fiscal months change FiscalYear = 1 to FiscalYear <= 2 in
the above where clause.

for the Oct thru March use Fiscal Month <= 6

You can add all kinds of columns to the fiscal table (calculate first 6
months in fiscal year FY08 only on Friday or only on weekdays etc...).

Think of it as (everywhere I have a date I also have columns for Fiscalyear,
fiscaldayofyear, fiscalmonth, fiscalquater etc...)

You only have to make sure your table has rows for dates you will be using.

HTH,
Mark

Fiscal YTD Filter

Jody

KARL DEWEY

Ken Sheridan

Ken Sheridan

Mark Andrews

Jody

KARL DEWEY

Mark Andrews