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!
- First, let’s group by month. To do that, we’ll need to isolate the month portion of our date. Use the column menu to select ‘Extract Date Part’ and choose ‘month’. You should now have a new column with the month represented by the numbers 1 through 12. Create a group with this new column as a key. Sort Ascending, so that 1 is listed first.
Note: ‘Truncate Date’ keeps the year associated with the month, while ‘Extract Date Part’ keeps just the month data. Because we want to look at the same month over several years, it is important to use ‘Extract Date Part’.
- Next, we’ll create a second group by year. Once again, we’ll use ‘Extract Date Part’ this time selecting year. Create a second group using your year column. Sort this column Descending, so the most recent year is on top.
- Calculate your monthly sales by aggregating your daily or weekly sales data. You can use the column menu to choose ‘Aggregate Column’ then ‘Sum’. The aggregate column should be under the ‘year’ group. This will give you all of the sales that happened in a specific month in that specific year.
- To calculate the year over year data for the month, we’ll use the Lead function. [Monthly Sales] / Lead([Monthly Sales], 1) Use the column menu to choose Format then Percentage to change the column values to percentages.
Your Worksheet as a whole 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 Worksheet is grouped and sorted. If you would like to see the year over year values with the worksheet in a different configuration, you should build a new Worksheet on top of the Worksheet you used to calculate the year over year values. Creating a new Worksheet that is linked to the calculation worksheet turns all of the calculated values into static values, allowing you to rearrange the worksheet without interrupting the window functions.