Pages

Thursday, December 11, 2014

Salesforce: Report Time Frame

In Salesforce, when we run a report, there is a Time Frame option predefined and available for us to use, see below list. But it will not have ALL date range as we want.


Fiscal Year
Current FY
Previous FY
Previous 2 FY
2 FY Ago
Next FY
Current and Previous FY
Current and Previous 2 FY
Current and Next FY

Fiscal Quarter
Current FQ
Current and Next FQ
Current and Previous FQ
Next FQ
Previous FQ
Current and Next 3 FQ

Calendar Year
Current CY
Previous CY
Previous 2 CY
2 CY Ago
Next CY
Current and Previous CY
Current and Previous 2 CY
Current and Next CY

Calendar Quarter
Current CQ
Current and Next CQ
Current and Previous CQ
Next CQ
Previous CQ
Current and Next 3 CQ

Calendar Month
Last Month
This Month
Next Month
Current and Previous Month
Current and Next Month

Calendar Week
Last Week
This Week
Next Week

Day
Yesterday
Today
Tomorrow
Last 7 Days
Last 30 Days
Last 60 Days
Last 90 Days
Last 120 Days
Next 7 Days
Next 30 Days
Next 60 Days
Next 90 Days
Next 120 Days

When you implement Custom Fiscal Year, you will get additional option:
Fiscal Period
Last FP
This FP
Next FP
Current and Previous FP
Current and Next FP

Fiscal Week
Last FW
This FW
Next FW


Use Case 1: get all opportunities with Close Date = Last 3 months, example: now = 11 Dec 2014.
So, we would like to show all opportunities close date from 1 Sep 2014 – 30 Nov 2014.
With above available Time Frame option, we do not have option to filter by Last 3 Months.

Solution:
In the Time Frame, leave it Custom and set both From and To = blank
Add a filter Close Date equals LAST 3 MONTHS
See this blog for sample of using Relative Date Value


Use Case 2: get all opportunities with Close Date = Year-to-date (YTD), as of Wikipedia, YTD = Year-to-date is a period, starting from the beginning of the current year, and continuing up to the present day.  
With above available Time Frame option, we do not have option to filter by YTD.

Solution:
In the Time Frame, leave it Custom and set both From and To = blank
Add a custom formula field in Opportunity with return type = Chekcbox, applied this formula YEAR(Today()) = YEAR(CloseDate) && Today() >= CloseDate
Example the field name is YTD, use this field as report filter YTD equals True