We have a request to have a chart with opportunity amount in both monthly total and cumulative YTD. Salesforce report has the cumulative option for a line chart, but unfortunately, as mentioned in the first paragraph, we can't use a field more than once in a chart, while to create a formula field for this scenario of dynamic values seems like not possible. The easier option is to export the data and easily built it in Excel, but it would not be stored in the platform.
simple bar chart only shows the total monthly amount
change to line bar chart and enable the Cumulative option
add the new measurement, but only can select different field, in this case, I use Count, plot as line chart and as the second axis.
create new formula field Amount clone, but because the main chart is a bar chart, the Cumulative option is not available for the additional measurement.
Now, let's see how is the data looks like in the report for the last chart:
So, if we can get the cumulative value in the last column, instead of the same value, then we can achieve the report requested.
For row 1, it would be just that row itself.
For row 2, it would be row 2 amount + row 1 amount.
For row 3, it would be row 3 amount + row 2 amount + row 1 amount.
Remember, there is a function called PREVGROUPVAL() in the Custom Summary Formulas for the report. We can use this function for this need. This function calculates values relative to a peer grouping. If there’s no previous grouping, the function returns a null value.
This function returns the value of a specified previous grouping. A “previous” grouping is one that comes before the current grouping in the report. Choose the grouping level and increment. The increment is the number of columns or rows before the current summary. The default is 1; the maximum is 12. You can only use this function in custom summary formulas for reports.
PREVGROUPVAL(summary_field, grouping_level [, increment])
From here, see that you can adjust the incremental of previous grouping.
Adding Custom Summary Formula with PREVGROUPVAL() function allow us to get this table
Then we can easily add this chart to the report:
Step by step:
1. From your summary or matrix report, create Custom Summary Formula, in this example, I called it Cumulative Amount.
2. In "Where will this formula be displayed?", select "Grouping 1:".
AMOUNT:SUM
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,1)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,1), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,2)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,2), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,3)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,3), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,5)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,5), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,6)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,6), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,7)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,7), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,8)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,8), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,9)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,9), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,10)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,10), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,11)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,11), 0)
You can adjust the formula as necessary, change AMOUNT:SUM with RowCount if you need to show the number of records instead of Opportunity Amount.
Note: you can use this solution if your report has the maximum grouping of 13, because the maximum incremental for PREVGROUPVAL() is 12.
4. Add Vertical Bar Chart with Y-axis is the main amount, then add measure using Custom Summary Formula created in step 1-3, select Plot as Line Chart.
You also can enable "Plot on Second Axis" to get second Y-axis values for the line chart values.
Reference:
- PARENTGROUPVAL and PREVGROUPVAL
- Ultimate Salesforce Reports: bars with a cumulative line in one report chart
No comments:
Post a Comment