Create a year-over-year comparison formula in Cloudbeds Insights

Introduction

Comparison mode displays the same report metric across multiple periods. A comparison formula adds a calculated column so you can measure the difference between those period values instead of reviewing them only side by side.

The workflow below creates a Financial report that subtracts last year's Transaction Amount from this year's Transaction Amount. Use the same sequence to configure the periods, add both compared metrics, and save the YOY Change column.

Table of contents

• Before you begin
• Create a year-over-year comparison report
• Understand the comparison result
• Troubleshooting

Before you begin

Creating and saving this custom comparison report requires Cloudbeds Insights and the appropriate Author permission for the report type. Because this example uses the Financial report type, the user needs Financial Author permission. Review Role privileges for the complete permission details.

Comparison formulas work only in reports created with comparison mode enabled. If your property does not have Cloudbeds Insights, you cannot create this custom formula, but you can use the Comparison Formula included in certain reports provided by Cloudbeds. See Use the Comparison Formula without Cloudbeds Insights for the supported reports.

Create a year-over-year comparison report

Complete the three steps below in order to create the report, configure the periods to compare, and add the YOY Change column.

Step 1: Create a report with comparison mode

Start from Reporting and create a Financial report with comparison mode enabled. This setting makes the period and comparative-range controls available in the report.

Open the Main Menu in the upper-left corner of Cloudbeds PMS, and select Reporting

1. Click Add, and select Report.

2. In the New Report window, select Financial as the Report Type.
3. Enter a Report Name, such as Comparison.
4. Select Enable comparison mode.
5. Click Create.

Confirm the report type, name, and comparison-mode setting before creating the report:

Step 2: Configure the comparison periods

Once the report is open with comparison mode enabled, configure This year and Last year as the two periods the formula will compare.

1. In the open report, click the Period Comparison Mode button
2. Under Select Data Field, select Service Date.
3. Under Compare Periods by, select Year(s). The report displays three default periods: Last year, This year, and Next year.
4. In the Periods area, click Edit.

5. Configure the two year-over-year periods:
   1. Edit the first period, change its date range to This year, and click Confirm.
   2. Edit the second period, change its date range to Last year, and click Confirm.
   3. Edit the third period, and click Delete.

The animation shows how to edit the first two periods and remove the third period:

6. Click Apply at the bottom of the comparison panel.
7. Click Save.

Step 3: Add the year-over-year comparison formula

After saving the two comparison periods, add a comparative range and configure a calculated column that subtracts last year's Transaction Amount from this year's Transaction Amount.

1. Click the Period Comparison Mode button
2. In the Periods area, click + Add, and select Comparative range.

Select Comparative range from the Add menu in the period-comparison panel:

3. In the Add comparative range window, enter YOY Change as the comparative range name, select This year under Show range after, and click Confirm.

4. In the new YOY Change area, click + Add Column.

The YOY Change area appears below the configured periods:

5. In the Add column window, configure Metric A:
   • Column name: YOY Change
   • Format: Number
   • Data field: Transaction Amount
   • Metric: sum
   • Base range: This year

Metric A represents this year's summed Transaction Amount:

6. Click Add to add Metric B.
7. For Metric B, select Transaction Amount as the Data field, sum as the Metric, and Last year as the Base range.
8. Use the formula buttons to create A - B, and click Confirm.

Metric B represents last year's summed Transaction Amount, and the formula subtracts it from Metric A:

9. Click Apply to display the YOY Change column in the report.
10. Click Save to retain the comparison formula in the report.

The YOY Change column appears beside the two base periods:

Understand the comparison result

The YOY Change column uses A - B, where Metric A is this year's Transaction Amount and Metric B is last year's Transaction Amount. The result shows the absolute difference between the two periods.

Troubleshooting

Expand the question that matches your access or package situation.

Next Operational Step

Now that the YOY Change column is saved, run the report with the dates needed for your analysis. Use Create and manage reports with Cloudbeds Insights when you need to organize or export the report.