To create effective Year over Year Reports that continue to roll forward each year, first we will need to create custom checkbox fields on each object to identify records that meet your desired Report date range (example: This Month Last Year).
In this Article, we will cover how to add checkbox fields to the Appointment, Sale and Project objects in order to identify records that were...
- Appointment Set On date during the previous Month for the previous Calendar Year
- Appointment Set On date during the current Month for the Previous Calendar Year
- Appointment Set On date during the current Week of the Previous Calendar Year
- Appointment Date during the previous Month for the previous Calendar Year
- Appointment Date during the current Month for the Previous Calendar Year
- Appointment Date during the current Week of the Previous Calendar Year
- Sold On Date during the previous Month for the previous Calendar Year
- Sold On Date during the current Month for the Previous Calendar Year
- Sold On Date during the current Week of the Previous Calendar Year
- Project Completed On date during the previous Month for the previous Calendar Year
- Project Completed On Date during the current Month for the Previous Calendar Year
- Project Completed On Date during the current Week of the Previous Calendar Year
Note: Only System Administrator users will be able to create custom fields.
Part 1: Create Custom Formula checkbox Fields
Go to the Setup menu
Go to the Create > Objects menu
Select the Appointment object
In the Custom Fields and Relationships menu, click the New button
Create three new Formula checkbox Fields and use the following formulas:
Set Last Month Last Year:
IF(MONTH( i360__Appt_Set_On__c ) = (MONTH(TODAY())-1) ,true, false)
Set This Month Last Year:
IF( (MONTH( i360__Appt_Set_On__c ) = MONTH (TODAY())&& DAY( i360__Appt_Set_On__c ) <= DAY(TODAY())), true, false)
Set This Week Last Year:
IF( ((ISOWEEK( i360__Appt_Set_On__c )) = (ISOWEEK(TODAY()))), true, false)
The fields will look something like this:
Next, create three more custom Checkbox formula fields on the Appointment object and use the following formulas:
Run This Week Last Year:
IF( ((ISOWEEK( i360__Start__c )) = (ISOWEEK(TODAY()))), true, false)
Run This Month Last Year:
IF((MONTH( i360__Start__c ) = MONTH (TODAY())&& DAY( i360__Start__c ) <= DAY(TODAY())), true, false)
Run Last Month Last Year:
IF(MONTH( i360__Start__c ) = (MONTH(TODAY())-1) ,true, false)
This fields you created will look something like this:
Next, navigate to the Objects menu again
This time choose the Sale object.
Create three custom Formula checkbox fields on the Sale object and use the following formulas:
Sold Last Month Last Year:
IF( MONTH(i360__Sold_On__c) = (MONTH(TODAY())-1) ,true, false)
Sold This Month Last Year:
IF( (MONTH( i360__Sold_On__c ) = MONTH (TODAY())&& DAY( i360__Sold_On__c ) <= DAY(TODAY())), true, false)
Sold This Week Last Year:
IF( ((ISOWEEK( i360__Sold_On__c )) = (ISOWEEK(TODAY()))), true, false)
Next, navigate to the Objects menu again
These last fields we will create on the Project object.
Create three custom Formula checkbox fields and use the following formulas:
Completed Last Month Last Year:
IF( MONTH(i360__Completed_On__c ) = (MONTH(TODAY())-1) ,true, false)
Completed This Month Last Year:
IF( (MONTH( i360__Completed_On__c ) = MONTH (TODAY())&& DAY( i360__Completed_On__c ) <= DAY(TODAY())), true, false)
Completed This Week Last Year:
IF( ((ISOWEEK( i360__Completed_On__c )) = (ISOWEEK(TODAY()))), true, false)
Part 2: Create Reports to pull Current Year Data and Previous Year Data
Once you have created these fields, we can begin to build Reports and Dashboards to isolate these records.
You will create a Report for each Time Frame for the Current Year and the Previous Year, for example the Filter for Set This Month Last Year would be:
- Set On equals Previous Calendar Year
- Result not equal to Canceled
- Set This Month Last Year equals True
Change your Report Format to the Matrix report, and Group the Report by the Appointment Date.
Be sure to pull in the "Set" field and Sum the total number of Set Appointments. Drag and drop the Set field into the preview pane:
Choose to Summarize the Set data:
Save your Report.
The report you will want to compare this to would be Appointments Set This Month during the Current Calendar Year. That report Filter would look like this:
- Set On equals This Month
- Result not equal to Canceled
Be sure to change the formatting of this Report to Matrix and group on the Appointment Date by Calendar Month, and pull in the Set field and Summarize the Set data. The Report preview should look something like this:
Later, we will add these two Reports to a Dashboard using a Metric style chart to compare them side-by-side. You'll want to create additional Reports for Sets This Week (current) and Sets This Week Last Year, as well as Last Month (current) and Last Month Last Year. Allowing you to compare Last Year's data to this Year's data. Be sure your reports are in a Matrix format with the Set count summed.
Next, let's look at the filters for Run metrics year-over-year.
Filter the Appointment report on:
- Appointment date equals Previous Calendar Year
- Run equals 1
- Result not equal to Canceled
- Run This Month Last Year equals True
Change your Report Format to the Matrix report, and Group the Report by the Appointment Date.
Be sure to pull in the "Run" field and Sum the total number of Run Appointments. Drag and drop the Run field into the preview pane:
Choose to Summarize the Run data:
Save your Report.
Create another report to compare Last Year to this current year's Run data.
The current year Report will Filter On:
- Appointment date equals Current Calendar Year
- Run equals 1
- Result not equal to Canceled
Be sure to change the formatting of this Report to Matrix and group on the Appointment Date by Calendar Month, and pull in the Run field and Summarize the Run data. The Report preview should look something like this:
Save the Report.
Continue creating Matrix reports for the other date ranges you'd like to compare: This Week current and last year, Last Month current and last year, etc.
Next, you'll want to create Reports for Sales metrics year over year.
For our first Sales report we'll Filter on:
- Sold On equals Previous Calendar Year
- Status not equal to Canceled
- Last Month Last Year equals True
Change the Format to a Matrix style report. Group on the Sold On date by Calendar Month.
Be sure to pull in the Sold Price into the body of the Report.
Drag and drop the Sold Price field into the Report and be sure to Summarize the Sold Price data.
Next, we'll want to compare this Data to the current year. That Report filter would be:
- Sold On equals Last Month
- Status not equal to Canceled
Be sure to use a Matrix format, grouped by Sold On Calendar Month and Summarize the Sold Price data.
Save the Report.
Continue creating Reports for This Month (current) and This Month Last Year, as well as This Week (current) and This Week Last Year. Save the Reports. You'll use these within a Dashboard later.
Next we'll create the Projects Completion reports for year over year Project data.
The Projects report will filter on:
- Project Completion Date equals Previous Calendar Year
- Status equals Completed
- This Week Last Year equals True
Be sure to use a Matrix format for this Report and group by the Project Completed Date, Calendar Month. Be sure to pull in the Sold Price and Sum the Sold Price data. Your Report preview will look something like this:
Continue creating Reports for the This Month (current) and This Month Last Year, as well as Last Month Last Year and Last Month (current). Save each of the Reports to be used in a Dashboard.
Part 3: Create Dashboards to display Report data side-by-side
On the Reports Tab click New Dashboard
Adjust your column width to Wide for both columns.
Drag and Drop the Metric style chart into the Dashboard builder, drop one in each column to start
Switch to the Data Sources Tab and locate the "Last Year Appointments Set This Week" Report you saved earlier in Part 2 of this exercise.
Drag and Drop the Report into the Metric chart within the preview pane, and Name this chart. In my example I'm naming this chart "Previous Year Appointment Sets This Week"
If the data is not immediately formatted to your liking, use the Wrench icon to Edit the attributes of the Chart. Be sure to choose the Sum of Set as your Value.
Next we will add This Week's data from the current year to compare them side-by-side. Be sure to Name the chart something appropriate for User's viewing this Dashboard.
You'll want to continue adding all the Report metrics for Sets This Month (current) and Sets This Month Last Year, as well as Last Month (current) and Last Month Last Year, and any other time frames you'd like to compare within this Dashboard. The completed Dashboard will allow Users to compare Last Year's data to this Year's data and will look something like this:
You might choose to display Run metrics and Set metrics in the same Dashboard, or split them between two Dashboards.
Use the same method as shown above to display the Run Report metrics side-by-side in a Dashboard to compare the current Month with the same Month from the previous Year. The Dashboard example shown below utilizes three custom Fields and six Reports.
Here is an example of the Sales metrics Dashboard year over year with both Sale record count as well as volume.
Here is an example of the Completed Projects metrics Dashboard year over year with both Project Completed record count as well as installed volume. The Dashboard below utilizes two custom fields and eight (8) Reports to deliver these metrics.