Calculate Week Over Week Numbers
Once you calculate your weekly numbers, calculating week over week numbers becomes very simple when using the Lag or Lead functions. Lag and Lead are Window Functions that shift all of the rows in a column. The key to using Lag and Lead properly is to ensure that your 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.
Calculate Week over Week numbers using Lag
- Group your data by your date column. Open the column menu and choose Truncate Date > Week.
-
In the left panel, drag the column you would like to sum up into the group defined by [Week of Date]. For example, if your sales numbers were in the column [Sales Amount] you would drag [Sales Amount] under the [Week of Date] column in the left panel. Sigma will automatically apply the formula Sum to [Sales Amount]. Rename the column "Weekly Sales".
OR
Create a column next to [Week of Date] in the table. Enter the formula Sum([Sales Amount]) in the formula bar. Rename your column "Weekly Sales". -
Sort weekly sales data by date in Ascending order, ensuring a unique sort order.
- Once you have your data sorted, create a new column next to you weekly sales column and enter in [Weekly Sales]/ Lag ([Weekly Sales])
- Change the column format to percent.
Calculate Week over Week numbers using Lead
-
Group your data by your date column. Open the column menu and choose Truncate Date > Week.
-
In the left panel, drag the column you would like to sum up into the group defined by [Week of Date]. For example, if your sales numbers were in the column [Sales Amount] you would drag [Sales Amount] under the [Week of Date] column in the left panel. Sigma will automatically apply the formula Sum to [Sales Amount]. Rename the column "Weekly Sales".
OR
Create a column next to [Week of Date] in the table. Enter the formula Sum([Sales Amount]) in the formula bar. Rename your column "Weekly Sales". - Sort weekly sales data by date in Descending order, ensuring a unique sort order.
- Once you have your data sorted, create a new column next to you weekly sales column and enter in [Weekly Sales]/ Lead ([Weekly Sales])
- Change the column format to percent.