Happy New Year!
Ever struggle with a problem for months, if not years, and then when the solution comes it’s so painfully simple that you have a bruise on the side of your head from where you keep slapping yourself?
Salesforce reporting is great when you want to look at the whole of a period of time. This fiscal year. Last month. Last year. Yesterday. 12/25/2010. It can also compare. This year vs last year. This month vs last month. Yesterday vs today.
What it doesn’t do out of the box is look at a fraction of a duration compared to the same fraction in a previous time period. April-May 2010 vs April-May 2009. December 31st this year vs December 31 last year. This fiscal year up to this date vs last fiscal year up to the same date last year.
We have our go-to report that gives us a picture of all income in a nice matrix separated by fiscal year over a 3 year period. Every organization tracks different types of income. This is just what we track.
The period of time is set by the interval on the report.
There is no way out of the box in reporting to compare 7/1/2008-12/31/2008 vs 7/1/2009-12/31/2009 vs. 7/1/2010-12/31/2010. So when we run this report we see the results of the entire fiscal year 2009 and 2010 and only up to where we are now in 2011. When we want to see how we’re tracking vs the same point in previous years, we end up running new reports with new Time Frames and then comparing.
I’ve Googled for a better answer. Asked around in Dreamforce and at user group meetings. I even asked in the Challenge Us group in the Dreamforce app.
The answers I received were usually something around rollup summary fields. Essentially I was advised to create a rollup summary field that has the date ranges I want and then report on that field. You can’t do relative fields in rollup summary fields, so instead of a simple TODAY() formula it would have to involve the actual date baked in to the formula. Need a new report tomorrow, then edit the field with tomorrow’s date.
Makes sense, but not practical. I needed a solution that any user who knows how to edit and run reports can use and modify as needed. I am the only one who can edit formula fields, with good reason.
The stupidly easy solution:
It does involve formula fields, but the difference is it’s set once and it’s done. No need to edit the field later when a report is run.
First field you’ll need is a formula number field on Opportunities. I called it “Close Month.” Note: there is already a Close Month field in reports, but that is only there for groupings. You can’t actually pull a number from it. If you think that will be confusing for users, name yours something else.
The formula: MONTH(CloseDate)
That’s it. This will just return the month as a whole number. So a donation with a close date of January 23rd will return 1 in this field. A donation on March 5th will return 3, and so on. Make the field visible on profiles but don’t add it to layouts.
The second field is also a formula number field called “Close Day.” I think you can guess what the formula is.
So that January 23rd donation will return 23 to this field, that March 5th donation will return 5.
Then it’s a simple matter of using filter criteria in the report around those new formula fields.
Keep the Time Frame set to the same overall duration (in this case, Current and Previous 2 FY) but then filter to only show the months (or days) that you want to compare.
For example, this is the filter that compares 7/1-12/31 over the current and previous 2 years:
Note: In Salesforce filters a comma in a filter means OR.
You could set 2 filters, one that says Close Month greater or equal than 7 and another that says Close Month less than or equal 12. But I thought it would be easier for folks to edit in one line.
December 31st is a big day for nonprofits. Here’s the filter I created on the exact same matrix report to show us online (credit card) donations from individuals (Households) this year compared to the past 2 years for that single day:
The possibilities are endless. And best yet, it’s something users can readily use without needing their administrator to do for them each time.
If there’s an easier way to do this, I can’t imagine what it could be.