Year Over Year Numbers by Month

Year over year data viewed by month can give us insights on how our business is doing while taking into account seasonality. In Sigma, you can calculate your data in just a few steps!

Screen_Shot_2021-07-30_at_4.40.27_PM.png

Group by Month

  1. Isolate the month portion of your dates. Create a new column next to your date column, and use the formula DatePart("month", [Date]) where [Date] is the name of your date column. This will give you a column of data with the month represented by the numbers 1 through 12. We'll call this column [Month of Date].
  2. Create a new grouping defined by [Month of Date]. You can do this in the left panel by dragging the column up to Groupings, or by clicking the + next to the Groupings heading.
  3. Sort the [Month of Date] column by ascending order.

    Screen_Shot_2021-07-30_at_4.40.19_PM.png

Group by Year

  1. Isolate the year portion of your dates. Create a new column next to your date column, and use the formula DatePart("year", [Date]) where [Date] is the name of your date column. This will give you a column of data with just the year. We'll call this column [Year of Date].
  2. Create a new grouping defined by [Year of Date] underneath the grouping defined by [Month of Date]. You can do this in the left panel by dragging the column up to Groupings, or by clicking the + next to the Groupings heading.
  3. Sort the [Year of Date] by descending order, so the most recent year is on top.

Calculate your Monthly Sales

  1. In the left panel, add the column with your sales data to the Grouping defined by [Year of Date]. Sigma will automatically apply the aggregate Sum. This will give you all of the sales that happened in a specific month in that specific year.
  2. Rename your column to "Monthly Sales".

Calculate the Year over Year Data for the Month

  1. Create a new column in the [Year of Date] grouping.
  2. Use the formula Lead to find the previous month's sales. Use the formula Lead([Monthly Sales]). Rename the column to "Previous Month".
  3. Create a new column in the [Year of Date] grouping.
  4. Use the new [Previous Month] column to calculate the percent change. Use the formula ([Monthly Sales] - [Previous Month]) / [Previous Month]
  5. Change the column format to percent.

Your workbook table now shows your year over year data by month. The values are calculated via window functions, which means that it is affected by how the table is grouped and sorted. If you would like to see the year over year values in a different configuration, you should create a child element which will use your year over year table as a source.

Creating a child table turns all of the calculated values into static values, allowing you to rearrange the table without interrupting the window functions.