Year over year sales by month

Year-over-year (YoY) data viewed by month can give you insights about how a business is doing, while taking time periods such as periodicity and seasonality into account. For example, a retail company may want to compare their December holiday sales to December sales of the last year. This tutorial uses the sample PLUGS data to show year-over-year sales by [Month of Date]. 

This tutorial uses the DatePart function and the Lead and Lag window functions. Window functions are special functions where the result depends on the value of adjacent rows (those within the "window"). Therefore, how you group and sort the data in the table affects the results. For more information see Window function overview and Groups and groupings. 

Here is what the table looks like when the tutorial is done:

YoY-final.png

Contents

Tutorial procedures

1 - Create a sales column

2 - Group by month

3 - Group by year

4 - Calculate monthly dales

5 - Calculate the year over year fata for the month

Use a child table for further explorations

 

Tutorial procedures

Step 1 - Create a sales column

Your data may already have a Sales column. The sample PLUGS data does not, so for this tutorial you create a new column called Sales.

  1. Create a new column, in one of the following ways:
    • Select a column and select Add new column from the column's menu.
    • Select a column and press Shift-+. 
    • From the left panel Columns list, click + Add new column.
  2. Enter this formula in the formula bar: 
    [Quantity] * [Price]
  3. Name the new column [Sales]. Use the column's menu Rename column option, or press Shift-R. 
  4. From the column's menu, change the format to Currency.
    Your new column looks like this:
    YoYtutorial-create-sales-column.png

 

Step 2 - Group by month

These steps group the dates by month. 

  1. To use the month data, apply the DatePart function to the [Date] column.
    Create a new column and use the formula: 
    DatePart("month", [Date])
    Sigma displays a new column with the month represented by the numbers 1 through 12, and automatically names it [Month of Date].
  2. Create a new grouping defined by [Month of Date], in one of the following ways:
    • In the left panel, drag the [Month of Date] column up to the Groupings section.
    • Click the + Add Grouping menu next to the Groupings heading.
    • Right-click the column and select Group Column.
    • Select the column and type Shift-G. 
  3. Sort the [Month of Date] column by ascending order, so that month 1 (January) is on top. Click the column's menu and select Sort ascending. 
  4. Collapse the months using the +/- button in the column header. 
    Your table looks like this:
    YoYtutorial-group-monthofdate.png

Step 3 - Group by year

These steps group the dates by year.

  1. Use the DatePart function to isolate the year. Create a new column (as you did in the Step 1 section) and use the formula: 
    DatePart("year", [Date])
    Sigma displays a new column that aggregates the years by month and automatically names the column [Year of Date].
    Tip: Another way to do this is to duplicate the [Month] column, then truncate the new column to Year. Use the right-click menu on the column for duplicating and truncating.
  2. Create a new grouping defined by [Year of Date]. Either:
    • In the left panel, drag the [Year of Date] column up to the Groupings section, or
    • Use the + Add Grouping menu next to the Groupings heading.
      Tip: Make sure that this is a new, separate grouping from the Month of Date grouping.
  3. Use the +/- button in the column header to collapse the [Year of Date].
  4. Sort [Year of Date] by descending order, so that the most recent year is on top.
    Your table looks like this:

    YoYtutorial-group-yearofdate.png

4 - Calculate monthly sales

These steps sum the sales for each month within each year. 

  1. In the left panel Grouping section, use the + Add calculations menu to add a new aggregate column to the [Year of Date] grouping. (Do not add it to the [Month of Date] grouping because that would show one value for the month over all years, which is not what is needed here.) Select the [Sales] column.
    Sigma automatically applies the aggregate Sum. The new column, named [Sum of Calc], shows all of the sales that happened in a specific month in that specific year.
  2. Rename the column [Monthly Sales].
    Your table looks similar to the following image. You can use the -/+ icons to expand or contract the groups. 
    YoYtutorial-monthlysales.png

5 - Calculate the year over year data for the month

These steps get the same month sales for the prior years.

  1. Create a new calculations column in the [Year of Date] grouping. Select + Add new column. 
    Sigma adds a new column to which you can apply a formula.  
  2. Use the Lead function to get the last year's same month sales. Use the formula: 
     Lead([Monthly Sales])
  3. Rename the column [Prior Year Monthly Sales]. 
    prior-year-monthly-sales.png
  4. Create another new column in the [Year of Date] grouping. 
  5. Use the new [Prior Year Monthly Sales] column to calculate the percent change. Use the formula: 
     ([Monthly Sales] - [Prior Year Monthly Sales]) / [Prior Year Monthly Sales]
  6. Rename the column to [YoY by Month]. 
  7. Change the column format to percent.
    YoYbyMonth.png
    Your table now looks like the one at the top of this page.

Use a child table for further explorations

To see YoY values in a different configuration, create a child element using this table as a source. Creating a child table turns all of the calculated values into static values, allowing you to rearrange the new table without interrupting the window functions.

Was this page helpful?
Yes No