1. Modeling
  2. Dimensions
  3. Time
  4. Model Calendar
  5. YoY Comparisons using Multi-Year Timescales

Year-on-Year (YoY) comparisons can be developed using line items subsets and the COLLECT function to avoid creating a YoY calculation for each and every line item. The process creates a separate module that uses a line item subset based on the line items in a data source module.

A YoY comparison can be set up for any number of years and any number of line items using just three formulas:

  • The COLLECT function to collect source data.
  • The POST function to post results on 12 months for comparison.
  • A YoY% calculation to calculate the current year values as a percentage of last year's. The YoY% uses a summary method of formula to correctly calculate quarterly and annual totals.

The advantage of using a line item subset as a hierarchical dimension is that you're free to do whatever calculations are needed within the line items and it's maintenance free. When you add line items to the source module, they will automatically be added to the line item subset.

The Variance Reporting section of Versions explains how to work with versions for YoY comparisons and for variance reporting across model timescales.