Year-on-year analysis
Using Matillion for Year on Year and Month on Month analysis.
Year On Year (also 'Year Over Year' or 'YOY') analysis is a popular method used to estimate the health of a business or investment through understanding whether its financial performance is improving, worsening or stagnating.
Matillion’s Lead/Lag Component provides the functionality to do Year on Year or Month on Month analysis out of the box. To illustrate how this is done, we first introduce an example table with revenue from products 'A' and 'B' by month and year, below:
The Lead/Lag Component can be easily used to compare one month’s revenue to the previous month’s revenue. This is done by configuring the Component to partition the data by product and then within each partition order by year and then month. This component utilises Windows functions – Lead and Lag. The functions work on a given partition, or window, of data and a result is returned for every row in that window.
In the case of this example, the data window is the product as every product needs to be treated separately. Then, within each product, the data needs to be ordered first by year and then by month so the product revenue is in chronological order. Matillion ETL will then create a new column with a name as specified to be equal to the revenue for the previous row:
In the same way it is possible to do year on year analysis by specifying the lag value to be 12 months:
A calculator component can then be used to build on this and calculate the percentage increase:
In the calculator a new column can be created to calculate the % increase in revenue from last year:
