Creating Fiscal Period Formula Fields in a Custom Fiscal Year Org
Mar 7, 2006If your business’s fiscal year is not based on the Gregorian calendar but follows a different structure consisting of fiscal weeks, periods and quarters deviating from the regular calendar year, you can set up your fiscal year structure using the Custom Fiscal Year (CFY) feature in Salesforce.
For example, your company may run on a “4-4-5” structure and a 13-week quarter may be represented by three periods of four, four, and five weeks rather than by three calendar months. Or may be your fiscal years consist of 13 fiscal periods consisting of 4 weeks each.
As specified on the CFY enablement page in setup as well as in online help and release notes, a side effect of enabling the CFY feature for an organization is that you will not be able to use the fiscal period columns (Fiscal Year, Fiscal Quarter, and Fiscal Period) in opportunity, opportunity with product, opportunity with schedule reports or opportunity list views otherwise available in non-CFY organizations.
Here is the tip: Using the formula field capability in Salesforce, you can create custom fields on the opportunity object that can calculate the fiscal year, fiscal quarter, fiscal period or a combination of these for you. These custom fields can then be exposed in all opportunity reports and list views and you can also filter by values in those fields. If you were using the fiscal period columns in opportunity reports prior to enabling CFY, you can recreate the columns via this formula field approach to retain reporting functionality that you may have been used to.
Here is how you do it:
1. In App Setup, go to Customize/Oportunities/Fields
2. Create a new custom field of data type “formula” and formula return type “text”.
For example, create the field “Fiscal Period” with the following formula:
if({!CloseDate} >= date(2009,
1, 3), "2009-Q4",
if({!CloseDate}>= date(2008,
10, 4), "2009-Q3",
if({!CloseDate}>= date(2008, 7,
5), "2009-Q2",
if({!CloseDate}>= date(2008, 3,
29), "2009-Q1",
if({!CloseDate} >= date(2008,
12, 29), "2008-Q4",
if({!CloseDate} >= date(2007,
9, 29), "2008-Q3",
if({!CloseDate}>= date(2007, 6,
30),"2008-Q2",
if({!CloseDate}>= date(2007, 3,
31), "2008-Q1",
if({!CloseDate} >= date(2007,
12, 30), "2007-Q4",
if({!CloseDate} >= date(2006,
9, 30), "2007-Q3",
if({!CloseDate}>= date(2006, 7,
1), "2007-Q2",
if({!CloseDate}>= date(2006, 4,
1), "2007-Q1",
if({!CloseDate} >= date(2006,
1, 31), "2006-Q4",
if({!CloseDate}>= date(2005,
10, 1), "2006-Q3",
if({!CloseDate} >= date(2005,
7, 2), "2006-Q2",
if({!CloseDate}>= date(2005, 4,
2), "2006-Q1", null))))))))))))))))
The date values in the formula depend on a company’s specific fiscal year calendar, and the formula may look quite different for your business. The above formula populates an opportunity custom fiscal period field for the following calendar:
Fiscal Year
2006
Q1
4/2-7/1
Q2
7/2-9/30
Q3
10/1-1/30/2006
Q4
1/31-3/31/2006
Fiscal Year
2007
Q1
4/1-6/30
Q2
7/1-9/29
Q3
9/30-12/29
Q4
12/30-3/30/2007
Fiscal Year
2008
Q1
3/31-6/29
Q2
6/30-9/28
Q3
9/29-12/28
Q4
12/29-3/28/2008
Fiscal Year
2009
Q1
3/29-6/27
Q2
6/26-9/26
Q3
9/27-12/26
Q4
12/27-4/3/2009
3. Expose the fiscal period field as a column or filter in all relevant opportunity reports such as for example opportunity, opportunity with products, opportunity with products and schedules or opportunity forecast report.
4. Expose the field as a column or filter in opportunity list views as needed.
This is what a fiscal period field created with the formula above could look like on the opportunity detail page:
Bookmark
Is there a way to leverage the native Fiscal Year, Fiscal Quarter, Fiscal Period, and Fiscal Week features via a formula, rather than recreating that information in a complex formula field?
Posted by: Chris Whiffen | September 02, 2009 at 02:13 PM