SAC Year over year comparison a brief

Let's incorporate year-over-year comparison and variance analysis into your SAP Analytics Cloud report. Here's how to do it:

Adding Year-Over-Year Comparison and Variance Analysis

1. Create Year Variables (if necessary):
   • If your "Period" dimension doesn't already have a separate "Year" attribute, you might need to create calculated dimensions or variables to extract the year.
   • For example, if your "Period" is in YYYYMM format, you can use a formula to extract the year.
2. Create Restricted Measures for Previous Year:
   • Use restricted measures to calculate the values for the previous year.
   • In the table builder, create a new calculated measure.
   • Use the RESTRICT function to filter the measure based on the previous year.
   • Example logic. If your year is a variable called year, then the previous years logic would be year -1.
   • Example SAC formula:
     • RESTRICT([Balance], [Year] = [Year Variable] - 1)
   • Repeat this for each measure you want to compare.
3. Create Variance Measures:
   • Create calculated measures to calculate the absolute variance and percentage variance.
   • Absolute Variance:
     • Subtract the previous year's measure from the current year's measure.
     • Example SAC formula:
       • [Current Year Balance] - [Previous Year Balance]
   • Percentage Variance:
     • Divide the absolute variance by the previous year's measure.
     • Example SAC formula:
       • ([Absolute Variance] / [Previous Year Balance])
     • Format the percentage variance measure as a percentage.
4. Add Measures to the Table:
   • Add the restricted measures (previous year), absolute variance measure, and percentage variance measure to your table.
   • Arrange the columns to make the comparison clear (e.g., Current Year, Previous Year, Absolute Variance, Percentage Variance).
5. Add Year Input Control (Optional):
   • If you want users to be able to select the year for comparison, add a year input control.
   • Configure the input control to filter the "Year" dimension or variable.
   • Make sure that the year variable that you created to calculate the previous year, is linked to the year input control.
6. Formatting and Conditional Formatting:
   • Format the measures appropriately (e.g., number formatting, currency symbols).
   • Use conditional formatting to highlight significant variances (e.g., large increases or decreases).

Detailed Implementation Steps

1. Year Extraction (If Needed):
   • If your "Period" dimension is in a format like "YYYYMM," create a calculated dimension or variable to extract the year.
   • Example formula, if period is a string "SUBSTR([Period], 0, 4)"
   • If the period dimension is a date, then use the year function. YEAR([Period])
2. Restricted Measures:
   • In the table builder, click "Add Calculated Measure."
   • Name the measure "Previous Year Balance."
   • Enter the RESTRICT formula, replacing [Balance] and [Year] with your actual measure and year dimension/variable.
   • Repeat for other measures.
3. Variance Measures:
   • Create a calculated measure named "Absolute Variance."
   • Enter the formula: [Current Year Balance] - [Previous Year Balance].
   • Create a calculated measure named "Percentage Variance."
   • Enter the formula: ([Absolute Variance] / [Previous Year Balance]).
   • Format the percentage variance measure as a percentage.
4. Table Configuration:
   • Add the "Previous Year Balance," "Absolute Variance," and "Percentage Variance" measures to your table.
   • Reorder the columns as desired.
5. Year Input Control (Optional):
   • Add an input control for the "Year" dimension or variable.
   • Link the year input control to the year variable that is used in the restricted measure calculations.
6. Formatting:
   • Apply number formatting to the measures.
   • Use conditional formatting to highlight significant variances.

Example Scenario

Let's say you have a "Balance" measure and a "Period" dimension in "YYYYMM" format.

1. Year Extraction:
   • Create a calculated dimension "Year" with the formula: SUBSTR([Period], 0, 4).
2. Restricted Measure:
   • Create "Previous Year Balance" with: RESTRICT([Balance], [Year] = [Year Variable] - 1).
3. Variance Measures:
   • "Absolute Variance": [Balance] - [Previous Year Balance].
   • "Percentage Variance": ([Absolute Variance] / [Previous Year Balance]).

By following these steps, you can add year-over-year comparison and variance analysis to your SAP Analytics Cloud report, providing deeper insights into your financial data.