How to calculate YoY change using self-join

Here is an example that shows how to calculate year-over-year change in a table with 2-year history of product sales. For the sake of simplicity, in this example sales happened only on the 1st day of month and only of one product. In real life, of course, multiple products can be sold every day.

The approach used here is called self-join. It means that data is joined to itself. Strictly speaking, in this example it’s not exactly true as we’re joining last year data to the current year’s data. However, since all data comes from a single source table we can call it a self-join.

As with any join, it’s crucial that the key fields used for joining (in this example it’s “Month-Day” and “Product ID”) must create a unique combination for each row in joined datasets.

We calculate “Month-Day” because for a YoY comparison we want to compare the same day of the same month but from the previous year. Month-day without a year allows us to match data for the same day/month but from different years.

Finally, the combination includes product ID. It’s redundant in this example, but in real life you should have Product ID possible in combination with other fields to uniquely identify each row (day) in each day of sales.

yoy-selfjoin.morph (4.8 KB)

Most wonderful!!! Thank you so much

Here is another variation that also uses a self-join:

  1. Calculate next year dates by using addyears().
  2. Merge (self-join) sales amount where current date matches next year date - it will bring into one row last year sales and current year sales.

yoy-selfjoin.morph (4.0 KB)