How I Solved This: Use Parent and Prior Group Value Formulas to Identify Trends Over Time

Key business problem 

Our Campfire client from our previous session loves how easy it is to enter good, reliable data in Salesforce. Before switching to Salesforce, they spent a LOT of time updating data in spreadsheets to try to get answers. Now, their planning team wants to use their Salesforce data to inform their decisions about how best to serve their Campers.

Background 

Because it was hard for Campfire’s planning team to manage data in spreadsheets, they often made schedules and planned based on hunches. This made it hard to identify trends over time. They had to copy data from one spreadsheet to another, then update a bunch of formulas to see the differences in year over year attendance. Month to month attendance information was always running behind as they waited for counselors to manually enter data. They were also curious about how each month’s attendance contributed to the total attendance for a year.

Using Previous Group Value and Parent Group Value Summary Formula rows in Salesforce reporting will allow the Campfire planning team to see their data in a whole new way!

How I solved it

You’ll be using these formulas in reports that have at least one grouped value. Let’s start with building a report that shows attendance grouped by year and by month.

1. Create a second date field

In order to group a report by both year and month, you’ll need a second date field that contains the same value as the field you want to report on. The easiest way to do this is to create a formula field that returns the value of the original date. I called my field “Date2” and didn’t include it on any page layouts.

2. Create a matrix report

A matrix report groups by both Rows and Columns. Group Rows by the Date2 field. Once you have a grouped row, you can add a Group Column field. Group Columns by the original date field.

To define how you’re grouping these dates, click the arrow next to the field in the header of the report and select Group Date By…. For your Grouped Row, select Calendar Year, and for the Grouped Column, select Calendar Month in Year.

Now you have a matrix report grouped first by Calendar Year and then by month.

I added additional fields like Number of Campers, Capacity, and Average Attendance Percent.  These fields will provide the planning team with some context on monthly attendance.

3. Create a summary formula column 

Now we’re ready to use PREVGROUPVAL! Click the arrow next to the Columns header on the left navigation and select Add Summary Formula.

Give your column a name—I called mine “Diff by Month”. Set the Output type to Number. On the left navigation, select Functions and start to search for “Previous”, then select PREVGROUPVAL and click Insert. We’re going to update the formula to take the current sum of Number of Campers and subtract from it the Number of Campers from the Previous Group (which is the month before). My formula ends up looking like this:

Camp_Attendance__c.Number_of_Campers__c:SUM - 
PREVGROUPVAL(Camp_Attendance__c.Number_of_Campers__c:SUM, 
Camp_Attendance__c.Date__c)

Make sure you validate your formula, then click Display to define where your formula should be applied. You’ll want to pick Specific Groups, then select the Date2 field for the Row Group and Date field for the Column Group, just like you assigned in the groupings on the report.

4. Add a chart to visualize changes

Adding a chart helps users visualize these changes from month to month. Because this report was run at the end of October, you’ll notice the steep drop in November because we haven’t had any Campfire sessions yet!

The planning team is really excited about seeing real-time data comparing data month to month and against last year!

5. Create a grouped report

For the next reporting challenge, we’ll use Parent Group Value to see how each month’s attendance contributes to the annual total. I’m going to filter to only include attendance records from last year, since the data will be skewed for the current year, because you’re comparing against a constantly changing variable of total attendance.

We’re still going to group by Date and Date2 fields, but this time both at the row level. Date2 grouped by Calendar Year, and Date grouped by Calendar Month in Year.

6. Create a summary formula column

We’re going to create another summary formula column using the Percent Output option. This time, we’ll call it “Percent of Annual Attendance”.

The Parent Group Value returns the value of a grouping in a level above the one containing the formula. In our example, the formula is taking the Number of Campers in a month and dividing by the annual sum of Number of Campers. The formula for this use case is:

Camp_Attendance__c.Number_of_Campers__c:SUM / 
PARENTGROUPVAL(Camp_Attendance__c.Number_of_Campers__c:SUM, 
Camp_Attendance__c.Date2__c)

Don’t forget to update where your formula should be applied. When using Parent Group Value, you’ll want to apply the formula to Specific Groups and select the lower grouping you defined on the report. In this case, the Date field.

7. Run your report!

You can now see how much of the total annual attendance was recorded in each month.

Business results

The Campfire planning team now has two useful reports to aid scheduling and service delivery. The longer they use Salesforce, the more they’ll be able to analyze historical trends and measure the outcomes based on changes they make to the program. They’re already thinking about what other kinds of reports they want!

Do try this at home

This example was real and it’s very simple. But the principle behind it can be applied in all sorts of ways. Let your imagination run wild! Here are a couple of examples to get your creative juices flowing:

Churn

How many donors (or customers) did you have year over year? Use PREVGROUPVAL to calculate these changes.

Year to date giving

Compare current year donation totals against the same time last year.

Let us know what you thought of this solution, and tell us how you want to use it with #AwesomeAdmins #HowISolvedThis on Twitter.

Resources

• Trailhead: Evaluate Report Data with Formulas 
• Salesforce Help: PARENTGROUPVAL and PREVGROUPVAL

Want to see more good stuff? Subscribe to our channel!