Salesforce Obscura: This quarter vs the same quarter last year, but ALIVE

A quick one that is the bane of many a fundraiser’s existence. You want to compare Q1 this year to Q1 last year. You’ve probably found tutorials that let you create a formula that outputs Q1, Q2 etc but then in your report filters you have to manually update the quarter each time it changes.

What if it could always represent a living quarter-to-date analysis of whatever the current quarter is? So as soon as Q2 hits, the report updates to show Q2 last year and Q2 this year, without anyone changing filters?

It’s a small thing, but everything in Salesforce that doesn’t rely on manually changing something is a win in my book.

Step 1: Create a Quarter formula based on close date

or any date that is about the record itself (created date for example). This is about what quarter the thing happened in, in the past when it happened.

This example uses the Close Date on the Opportunity and assumes a quarter system that aligns with the calendar year (e.g. Q1 is Jan, Feb, and Mar). Obviously you will need to adjust the formula below to match your real use.

Create a formula field on the object you want to report on (Opportunity in this example). It should output Text. I have called my field “Close Date Quarter.”

CASE( MONTH(CloseDate ), 1, “Q1”, 2, “Q1”,3, “Q1”, 4, “Q2”, 5, “Q2”, 6, “Q2”, 7,”Q3", 8, “Q3”, 9, “Q3”, “Q4”)

If you don’t like case statements then by all means use another approach. The goal is to print out Q1, Q2, Q3 or Q4 for whenever the record in question occurred.

Step 2: Create a Quarter formula based on today’s date.

This gives you something to say “what is this current quarter” in a way that is easy to use in reports. Same basic approach, but replace CloseDate with TODAY(). This gives you a living, always-updated indicator of what the current quarter is.

Create a formula field on the same object as step 1 (Opportunity in this example). It should output Text. I am calling the field “Current Quarter.”

CASE( MONTH(TODAY()), 1, “Q1”, 2, “Q1”,3, “Q1”, 4, “Q2”, 5, “Q2”, 6, “Q2”, 7,”Q3", 8, “Q3”, 9, “Q3”, “Q4”)

Step 3: Create a formula that compares the two.

You can obviously combine these into a single formula but I like having the close date quarter separate because it’s useful in other circumstances. And it seems easier for folks to understand and maintain when it’s 3 less complex pieces rather than one more complex piece. YMMV.

Create a formula field on the same object as steps 1 and 2 (Opportunity for this example). It should output a checkbox. I am calling the field “In This Quarter.”

IF(Current_Quarter__c = Close_Date_Quarter__c, TRUE, FALSE)

Step 4: Build your report.

Now you can build a very simple Opportunity report, whose filters select only opportunities where “In This Quarter” is checked (is True).

If you want, you can set a date range for just this year and last, or all time to see your historic performance in a given quarter.

You can (and should) group by Close Date (and further group the date by year or month, whichever you prefer).

From here you add a simple bar chart to represent your groupings and voila, it’s the right quarter every time you run it.

For people coming from other databases and banging your head against Salesforce, this article is also intended as a simple illustration of dealing with data as it moves through time in a system like Salesforce that offers no query language.

If you are transitioning from a relational database and SQL-style interaction to managing Salesforce, you would instinctively try to find a way to build a query that selects the sum of all opportunities in group 1 and the sum of all opportunities in group 2 and be able to compute the idea of “this quarter” on the fly.

In Salesforce, you usually have to break the problem down into steps, and aggregate or cache the desired information on the objects themselves — like creating a temporary column that uses a formula in Excel. We use formulas for this kind of thing all the time, since they are computed at runtime — when you observe them is when they run. As you can see from the above, you aren’t relying on a query builder to compute the core elements for you (like which quarter did this occur in, and what is the current quarter). And you’re deliberately putting transient information (current quarter) as a field on the object to aid in reporting.

It may be possible to solve this problem using row or summary level formulas in the report builder, but frankly I can’t teach my accidental admins how to do that and there are so many things it doesn’t solve, it’s a non-starter for me. I prefer to focus on the basic building blocks that let you solve almost any problem in Salesforce by knowing how the data interacts and what your constraints are. Hopefully you find some value in this!