How do we setup a report to show year-to-year comparisons by week or a particular date?

Question

We have Salesforce with the Nonprofit Starter pack. Does anyone know how to do year-to-year comparisons (based on week) based on a particular date range? Please see image below for format of report we are currently creating manually.

We use Designation Budget Year for financials and reporting. In order to get the comparison, 2014 Budget date range is going to begin 4/1/2013 with gifts that are restricted until Jan 1 2014. Same for future time periods. So in the screen shot below, you see Asks and Pledges for Designation Year 2014 from 4/1/2013 to 1/28/14 compared to Asks and Pledges for Designation Year 2015 from 3/25/14 to 1/20/15. Moving forward the week by week comparison would be the same except each January date will extend by one week each time.

Any advice would be appreciated.

Answer 1

Wendy - are you asking for something like this:

If so then you can just use the standard reporting, no snapshot required. The tricky part for a specific time period. You'll have to play with the report criteria to include only those dates from previous year and current year time frames. With the new field, week numbers (listed below) you could use that to filter on.

To start you need two different date fields to create a metric report. If you only have one date, ie donation date, then create a 'shadow/ghost' field. (formula field that is the same as your donation date). Then from there you can build your metric report with grouping by Calendar date:

Then use your other date field to group by calendar month: . However you want grouping by weeks, right. Well you'll have to create a formula field to do that. So instead of creating your shadow field create a new formula field called: Week Number. See this post, view the comments about half way down for what I think you need.

From there you group by the week number and calendar year. Hope this helps!

Answer 2

There are a variety of ways you could create your report. To me, the simplest would seem to be a "Joined Report". I say that because the previous calendar year's data won't be changing. Instead, only the current year's data is being updated. Essentially, you'd be creating the same report with two fiscal calendar years, joining them together (duplicating them actually & just changing the date ranges) then updating the report as the current year progresses. I highly recommend you go through the Salesforce1 Reporting Workbook paying particular attention to Tutorial 4 which covers Joined Reports and Tutorial 3 which covers Matrix Reports.

For Year to Year comparisons on a week by week basis, enabling History Tracking wouldn't be very helpful as it only holds a limited amount of data, usually the previous 3 months plus the current month (unless you create a custom object to store additional historical data). Currently, you don't have that data stored to work with.

To elaborate a bit, with History Tracking enabled, you could Set Up Historical Trend Reporting. See TUTORIAL 5: CREATING A PIPELINE PUSH REPORT in the Salesforce1 Reporting Workbook plus another document called REPORTING ON HISTORICAL CHANGES IN YOUR DATA which your Non Profit might find helpful for tracking its quarterly fundraising progress. That said, I don't believe that's the type of report which would satisfy your current reporting needs for donations and pledges on a year to year basis (called opportunities in regular SF language) and would recommend either a Joined Report or Matrix Report along the lines of what @Fifedog has suggested instead.

@Fifedog has a very novel approach to creating a Matrix Report that may work quite well for you. You may be able to make it satisfy your specific needs as it would line things up very close to what you've shown us. In any case, I think you'll discover that the documentation I've referenced will likely make things easier for you.

Answer 3

It sounds like you want reporting snapshots: https://help.salesforce.com/HTViewHelpDoc?id=data_about_analytic_snap.htm&language=en_US

Basically you create a custom object to hold the historic data you'd like to report on, then schedule a report to create records of your custom object.

This then allows you to do comparisons based on points in time.