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?
The problem:
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.
Ugh.
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.
Yup: DAY(CloseDate)
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.
Judi Sohn 
8 responses to “Ridiculously easy YTD comparison reports in Salesforce”
Wow! That’s super helpful! Now all my reports are getting rewritten! Thanks Judi!
Hi Judi,This is great! I’ve replicated this in my test instance of Common Ground so I can help our clients when they need a report like this.
Hi Judi,Thank you so much for this solution, I just started reading into this after I realized SF didn’t offer this report type out of the box. My question pertains to exactly what you mean when you say: “Make the field visible on profiles but don’t add it to layouts.” I know how to add fields to the layout pages, but how do I specify that the field shows up on profiles? Can I run these reports without including this information on the profile pages? You’re reply is greatly appreciated!Charlie
Hi Charlie… in Salesforce, a field can be “visible” but not necessarily be on any page layout. I know it’s confusing. Visible means that the profile can see the value in the field, whether it be on a record, dashboard, list, search box, or report. Or even through the API or a website page. So if you go to Setup -> Administration Setup -> Security Controls -> Field Accessibility you can see exactly what each profile can do on each field, and it has nothing to do with layouts on a page. Since the field in question appears on reports, it’s important that it’s visible for those profiles. But it’s just clutter for a page layout.Hope that helps.
Hi Judi,Thank you for your very prompt reply! I’m looking in the Security Controls drop down and I’m not seeing the Field Accessibility menu item that you mentioned. I went into SF help and training and noted that the “Modifying Field Access Settings” write up was only applicable to Enterprise editions and above (we’re currently using Professional edition). I know in the past when I’ve reported on other custom fields the fields had to be in the page layout otherwise they wouldn’t be available in the report builder. I’m guessing this may be another limitation of our current SF edition. I’ll still move forward with the fields even if I have to include them in the page layouts, just hoping there’s some way I can get around it! Thanks,Charlie
Yes, we’re using the Enterprise edition so it’s quite possible that I’m talking about something not accessible to you. I guess you could just create an “Ignore me please” section at the bottom of your record for these kinds of fields. Sorry.
Good call. Thanks for your advice, love the blog!
Oh Judi. You really don’t understand how great and helpful this entire post is for me. Thank you so much. Oh so much.