Would you give someone four dollars for four quarters? Probably not in most cases. Then, why would you run four reports for data and comparisons on four fiscal quarters?
In today’s post I wanted to cover how to add numerical and percentage changes in Salesforce reports based off of quarterly, yearly, and other groupings. Then we’ll use conditional highlighting to display positive results as green and negative results as red. Here is an example of the result we are looking for:
It’s recommended that you SAVE your report often to avoid losing any progress.
Note, in this post, I will be using custom formulas and the PREVGROUPVAL Summary Function. If you’re looking for more information on this, I would highly recommend checking out the Salesforce Reporting Workbook along with creating your own (free!) Salesforce Developer Org. Speaking of free, Trailhead also has many great resources for new and experienced Salesforce users. It is recommended to make any changes only in your own Developer Org. There’s also a great video here on PREVGROUPVAL and PARENTGROUPVAL.
To get started, I will be using Opportunities grouped by Fiscal Period of the Close Date using a summary report. I will also be using the Salesforce default fiscal periods (Jan-Mar = Fiscal Q1, Apr-Jun = Fiscal Q2, etc.) Please note that there are several different ways you could apply percentage and/or amount changes to reports, not just opportunity amounts as showcased in this example.
First, you will want to have a couple of opportunities filled out for each fiscal period of 2016 with the following filled out:
Close Date: (Choose date in each respective fiscal period.)
Stage: Closed Won
Amount: Any amount is fine; however, in the above image you’ll note that I made the total of Q2 Amounts greater than Q1 AND Q3 Amounts, leaving the total of Q3 Amount and Q4 Amount the same. This way, we are able to showcase an increase, decrease, and no difference in our Fiscal Period Groupings.
Feel free to mimic the amounts and close dates I have above. If you don’t, that’s fine, just note that the summary amounts and percentage/amount changes later in this demo will be slightly different!
Now that we have that out of the way, let’s get into the reason you’re probably here: reporting.
Once you’re logged into Salesforce, choose Reports toward the top, and choose New Report. Search for the Opportunities report, and choose Create.
We’ll see our filters toward the top, and we want to ensure the following filters are in place (feel free to change these around later for different results):
Note: If you’re not seeing certain filters, such as Territories, disregard.
Show: All Opportunities
Opportunity Status: Closed Won
Date Field: Close Date
Below that, in the Preview section, you’ll want to ensure the Report Format is changed from Tabular to Summary. (You may want to experiment with Matrix reports after this!)
You’ll see your headers below that, such as Opportunity Name. We only want to have the following headers:
Opportunity Name, Amount, Close Date, Account Name
Feel free to remove all other headers by dragging and dropping it all the way to the left where your fields are. We should be left with something like this:
Good progress! You should see all of the opportunities you have set up as Closed Won in 2016!
If you’re not seeing them, ensure you have a couple opportunities that are Closed Won with a close date in each fiscal quarter of 2016.
Now, in order to utilize our cool formula (including the PREVGROUPVAL function in particular), we are going to need this data grouped somehow.
In this example, let’s go ahead and group our results by Fiscal Period. To do this, search for Fiscal in the left search bar under Fields. You should see Fiscal Period appear. Go ahead and drag that from the left and drop it under your headers.
Note: You may see a section that says “Drop a field here to create a grouping.” If you’re not seeing this, click on Show, then choose Drop Zones. This can be particularly useful for when you’re just getting started with reports.
Now we have it grouped by Fiscal Period; however, we’re still not seeing summarized amounts for each quarter. Hovering over the Amount header, you should see a small drop-down appear. Choose Summarize this Field… then you should see another popup which will allow use to see the Sum, Average, Max, or Min of for our Amounts. We want to choose Sum and click Apply.
Now we should see our Amount summarized for each fiscal quarter along with a grand total!
Onto the most exciting part, formulas!
Now that we have our opportunities grouped by fiscal period with a summary of opportunity amounts, let’s add some formulas to this.
Clear out the Quick Find Search Area below fields in the left navigation if you haven’t already, and we should see Add Formula available.
You could drag either drag this over to the right of your rightmost column; or you could just double-click it. I prefer to add them right away and see if the results are what I expected, and edit as necessary. Let’s drag that over to the right of our Account Name.
This will bring up our Custom Summary Formula details. We’ll enter in the following:
Name: Amount Change
Decimal Places: 2
Under Where will this formula be displayed?:
Choose Grouping 1: Fiscal Period
Under Formula, click the Summary Fields drop-down, choose Amount > then Sum.
You should see AMOUNT:SUM drop into your formula area. To subtract the next field: add a space, a subtraction sign, and another space, then choose the ABS dropdown and select PREVGROUPVAL. Salesforce will give you some information about how to use this formula, along with a section asking us to choose an Eligible Grouping Level. As we only have one grouping level (Fiscal Period), it is the only one selected by default. Click Insert below that description.
You should be left with something like:
AMOUNT:SUM – PREVGROUPVAL(summary_field, FISCAL_QUARTER)
What is PREVGROUPVAL asking for? PREVGROUPVAL is asking us for a summarized field, which is going to be our Amount Sum in this case. It has automatically populated the grouping_level for us. In the help information, it also listed an increment, which could be placed after FISCAL_QUARTER to tell the function how many groupings to move backwards. By default, and without specifying the increment, it will only go back one grouping. It is able to “look back” as far as 12 groupings back in a report. Please see the video above for a more detailed explanation on this.
Let’s change our formula to include our summary_field of AMOUNT:SUM:
AMOUNT:SUM – PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)
What’s happening so far? Our formula will now take each summary amount (by fiscal quarter in this case) and subtract the previous fiscal quarter summary using the PREVGROUPVAL function. Note: We could have written this as AMOUNT:SUM – PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER, 1); however, the last 1 is redundant in this case as Salesforce presumes we want to “look back” at only the previous grouping (or one grouping before).
Great, let’s go ahead and click on Check Syntax and ensure we have the following before clicking OK:
Once we click OK, we should see a new column showing us an amount change based off the previous quarter (if applicable). Awesome! Here’s a sample of what mine looks like so far:
The amount change is working, which is great, but it doesn’t pop yet! I think we could add some color to that.
Just above our headers in the Preview section, we should see a Show drop-down. Let’s click that and choose Conditional Highlighting. This will bring up a new window.
In our first —Select Field– let’s choose our newly created Amount Change formula.
What’s happening here? In this area, conditional highlighting will do the following:
If an amount (Amount Change) is less than the first value, Color # 1.
If an amount is in between the two values, Color # 2. Note: If it is equal to value number 1, it will use color # 2 unless value # 1 and value # 2 are the same, then it will use color # 3.
If greater than or equal to the second value, Color # 3.
This is confusing, as a general rule of thumb just think: Color # 1 below Value # 1. Color # 2 between value # 1 and value # 2. Color # 3 above value # 2.
For the first value of Amount Change, let’s enter 0. For the second value, let’s enter 0.0001.
We could leave the amounts as 0; however, it will show a 0 change as green, which isn’t exactly ideal in my opinion. Use your own preferences here.
The last thing we want to do is change the middle color to nothing, as we don’t want to show a color representation for 0. It should look like this:
Click OK and hopefully you’ll have something along these lines at this point:
Let’s drag and drop our Add Formula again to the right of our Amount Change column to add our Percent Change column. For our Formula Details, let’s ensure the following:
Name: Amount Changer
Decimal Places: 2
Under Where will this formula be displayed?:
Choose Grouping 1: Fiscal Period
*Note that we changed the format from number to percentage.
Feel free to practice using the drop-downs to add the following formula, or just copy and paste:
IF(PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER) <> 0, (AMOUNT:SUM – PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)) / PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER), NULL)
What’s happening here? The IF statement could be reworded as: IF(Check Something and see if it’s true or false, if it’s true do this, otherwise do that)
This statement is making sure that the previous grouping is not equal to zero… if that is true (true that it’s not equal to zero), it will take the current group sum, subtract the previous grouping sum, and then divide that amount by the previous grouping sum to get a percentage change.
If it is false (false that it’s not equal to zero), it will return a null value.
Once you have the formula in place, click on Check Syntax to verify that the formula has no errors, you should this:
Go ahead and click OK, and you should see a preview of your results. All we need to do now is add conditional highlighting to the Percent Change.
Click the Show drop-down and then choose Conditional Highlighting… Enter the exact same values, removing the middle color, and click OK:
***Click Save to save your report. If this is your first time saving, you’ll need to specify a name and folder location. Feel free to pick a name and folder of your choice.
Now we can click Run Report toward the top to see our results.
What about years?
For Fiscal Years, (after removing your groupings) repeat the above process and group by Fiscal Years instead of Fiscal Quarters. Note: You will have to change the formulas from FISCAL_QUARTER to FISCAL_YEAR. Don’t forget to change your filters on top to as far back as you’d like to go (2015 for example).
What about other time-frames like weeks, months, etc?
For other time-frames, you will want to do the following:
Group your opportunities by their close date:
Choose Group Dates By and select your increment:
Change your formulas from FISCAL_QUARTER to CLOSE_DATE. That’s it! I grouped mine by month here:
A few other things to consider:
It could be beneficial to have two groupings (by fiscal year and fiscal quarter for example); however, each grouping (as far as I’m aware) will need it’s own column/formula.
You could end up with something like this:
Let me know if you have any other suggestions or comments!