Calculate week over week numbers

In Sigma, you can truncate a date column and then calculate week over week numbers using the Lag or Lead functions.

This example uses a version of the PLUGS sample data with an additional [Revenue] column ([Quantity] * [Price]).

Lag and Lead are window functions that shift all of the rows in a column. For more information see Window function overview.

The key to using Lag and Lead properly is to ensure that the data is sorted in a unique order, with no “ties” in the sort order. You can click the gear icon in the column menu to set up multi-column sort criteria to get a unique sort order.

Contents

Requirements

Calculate week over week numbers using Lag

Calculate week over week numbers using Lead

Related resources

Requirements

  • Your user account must be a Creator or Admin.
  • Access to the PLUGS sample data or another source containing date and revenue (or any other number you want to calculate) columns.

Calculate week over week numbers using Lag

  1. In the workbook table, select the Date column.
  2. From the date column's menu choose Group column.
    group-date-column.png
    Sigma groups the dates (by day, automatically) into a new column at the left of the table, and names the new column Day of Date. 
    new-day-of-date-column.png
  3. From the Day of Date column's menu choose Truncate Date > Week.
    truncate-to-week.png
    Sigma truncates the data to the week format and renames the column [Week of Date].
  4. Create a column next to [Week of Date] using one of the following methods:
    • From the [Week of Date] column's menu, select Add new column.
    • Select the [Week of Date] column and press Shift-+.
      Tip: These methods ensure that the new column is in the same grouping as [Week of Date]. If you were to use the Add Column menu in the left panel, the new column would be placed at the end of the table, outside the [Week of Date] grouping. For this tutorial you want the new column to be in the same grouping as [Week of Date]. Sigma automatically names the column [Calc]. The grouping looks like this:
      week-grouping.png
  5. Calculate the sum of revenue per week. Select the new column [Calc] and enter the following formula into its formula bar: 
    Sum([Revenue])
    Sigma calculates the values and renames the column [Sum of Revenue].sum-of-revenue-column.png
  6. Rename the column [Weekly Sales] using one of the following methods:
    • Double-click in the column header.
    • Use the [Sum of Revenue] column's menu and select Rename column.
    • Select the [Sum of Revenue] column and press Shift-R. 
  7. Select [Week of Date] and sort it in ascending order, ensuring a unique sort order.
    2023-07-21_16-57-36.png
  8. Create a new column next to [Weekly Sales]. Use one of the methods previously described in Step 4. Ensure that the new column is in the same grouping as the other two columns.  
  9. Enter the formula:
    [Weekly Sales]/ Lag ([Weekly Sales])
    The first row returns the calculation Null. This is expected because it is the first week, so there is no comparison to be made. You can scroll down to see other rows, but the formatting is not quite right yet.  
    new-calc-lag-column.png
  10. From the new [Calc] column's menu select Format > Percent. 
    format-percent.png
  11. Collapse the [Week of Date] columns to better see all the percentage week-over-week change in the [Calc] column.
    collapse-week-of-date.png
  12. The table should look like this:  
    week-over-week-final.png
  13. Change the column name as you like.

Calculate week over week numbers using Lead

  1. Follow the same steps as above, except:

    • In Step 7 sort the [Weekly Sales] in descending order. 

    • In Step 9 enter the formula: 
      [Weekly Sales]/ Lead ([Weekly Sales])
      The table should look like this:
      week-over-week-lead.png
  2. Change the column name as you like. 

Related resources

Was this page helpful?
Yes No