Compare Groups with PREVGROUPVAL() and PARENTGROUPVAL()
Learn About Summary Group Functions
Logical functions like IF() check whether a condition is true and return one value if true and another if false. Mathematical functions like SQRT() return a number’s square root. You can also use two powerful summary functions to compare values between groups: PREVGROUPVAL() and PARENTGROUPVAL().
Measure Values over Time with PREVGROUPVAL()
PREVGROUPVAL() lets you compare a specified grouping against a previous grouping in the report builder, making it a powerful tool for calculating how values change over time. PREVGROUPVAL() is most useful for evaluating report data that you’ve grouped by a date field, such as Close Month or Created Date.
If there’s no previous group in the report, PREVGROUPVAL() returns null (that is to say, it returns nothing; no value). For example, say that your report has 12 groups, January through December. For February, PREVGROUPVAL() returns January. For January, because January is the first group, there is no group before it, so PREVGROUPVAL() returns null.
The formula for PREVGROUPVAL() is written as PREVGROUPVAL(summary_field, grouping_level [, increment]). Let’s look at how it breaks down.
-
summary_field is the name of the grouped row or column (like AMOUNT:SUM).
-
grouping_level is the summary level (like CLOSE_MONTH).
-
[, increment] is the number of rows or columns that constitute each grouping.
The maximum value for the [, increment] field is 12. If an [, increment] isn’t specified, then the summary function automatically uses 1.
For example, let’s say Lance Park wants to know, for each month, the difference in sum of opportunity amount from the previous month in the report. He builds an opportunity report and groups it by Close Month. He then adds this summary formula, remembering to select Specific Groups and Close Month under the Display tab.
AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_MONTH)
Find Proportions with PARENTGROUPVAL()
PARENTGROUPVAL() returns the value of a specified parent group, which is any level above the one on which the formula is evaluated. If a report has only one group (like Lance’s opportunity report grouped by Close Month), then the report grand total values are “above” the one group. The report grand totals are always the most senior group.
The formula for PARENTGROUPVAL() is written as PARENTGROUPVAL(summary_field, grouping_level). Here’s how it breaks down.
-
summary_field is the value from the grouped row or column (like AMOUNT:SUM).
-
grouping_level is the name of a parent-level value in the report (like GRAND_SUMMARY for the overall total).
Ursa Major has received an alarming influx of cases recently, and Gabriela Livingston, the head of sales operations, wants to get to the bottom of it. She asks Ada Belawa, the company’s product support specialist, to create a case report grouped by type, to find out what percentage of cases belongs to each type. Let’s help Ada build a case report and add a custom summary formula with PARENTGROUPVAL() to tease out this information.
- Create a case report.
- Click Reports.
- Click New Report.
- From the Select a Report Type menu, search for and select Cases.
- Click Start Report.
- Group the report by Type.
- To open the outline pane, click Outline.
- Under Group Rows, click Add group, then search for and select Type.
- Click Save.
- Name the report
Case Percentages by Typeand set the Report Unique Name toCASE_PERCENTAGES_BY_TYPE.
- Click Save.
- From the Columns pane in the overview panel, click
, then select Add Summary Formula.
- For Column Name, enter
Percent of Total.
- For Formula Output Type, select Percent.
- In the Formula field, paste
RowCount/PARENTGROUPVAL(RowCount, GRAND_SUMMARY).
- In Display select Selected Groups.
- Verify that Row Group has Type selected.
- To verify that the formula is valid for the selected display option, click Validate.
- Click Apply.
- Click Save & Run.
Now the Percent of Total column you created appears in the report and gives Ada a better look at where Ursa Major’s quality control team can focus their efforts next.
Resources
