Sigma’s formula bar is a very powerful tool. In addition to using more basic Excel-like functions, users can leverage window functions which typically require SQL and some technical knowledge to handle in other tools. What makes these functions unique is that they are dependent on the sort order and grouping of columns. The “window” for a Window Function is defined by the grouping, and the result of a window function is determined only by the rows within that window. In this post, I’m going to talk about two of my personal favorite Window Functions - Lead and Lag. In Sigma, the Lead and Lag functions in grouped tables make complex SQL look easy and intuitive in a tabular format. Both functions require a column to be specified as well as a row offset as inputs, and the function returns the offset value from that column relative to a given row. Lead looks forward; Lag looks backwards. If no offset value exists (Lead of the last row of a window and Lag of the first row of a window), Null is returned unless otherwise specified in the function parameters.
As mentioned, in practice, the Lead and Lag functions require a sort order and grouping to be specified, so the first order of business is to prepare a table for our use case. In this example, let’s look at Point of Sale data for a fictitious company Plugs Electronics and compare Year-over-Year revenue.
Step 1: Prepare data
As always, we start with our base table. Once the data is prepped, create a child element table. This is the table we will use to do our grouping.
Step 2: Group table
Group the table by Year. Each year is going to be its own window.
Step 3: Add calculated column(s) at grouping level(s)
At the Year grouping level, let’s add a calculated column for Sum([Revenue]). We now have an easy way to look at annual revenue in this grouped table. Now we want to apply Lead() and Lag() to easily calculate some YoY comparisons.
Step 4: Sort table
As mentioned, Lead() and Lag() depend on the sorting order of the window in addition to the grouping level. In this example, since we are trying to calculate YoY comparisons, we know we want to sort by the Year column. Whether we sort it ascending or descending is determined by which window function we are going to use and how we want to look at this table. If I sort descending, a new calculated column using the Lead() function will show us the prior year’s revenue. If I sort ascending, a new calculated column using the Lag() function will show us the prior year’s revenue. Let’s sort descending.
Step 5: Use Lead()/Lag() function
Now that we have sorted Year descending, let’s add another calculated column next to Annual Revenue to grab the prior year’s revenue for a given year. Now that these values are at the same row level, we can create an additional column to calculate the YoY % change for a given year if we’d like.
And that’s it!