How to use the Period over Period Feature

Hey all! There’s a new and easier way to create time period analysis in tables! Period-over-Period Calcs

What is it? A way to add an auto-calculated column for period-over-period calculations. Think of this as an “easy button” for these calculations.

How to use it? Add a column like normal, but now there’s another option under “add column via…” Users are prompted to fill out a pop-up (like the Lookup pop-up) to determine what period they want to compare and what calculations to return. Sigma then automatically creates the correct grouping level, date truncations, and calculations needed.

Note this is using a new function DateLookback, which is basically a GrandTotalAggIF + InPriorDateRange

Step 1: Add a new column

Step 2: Choose the comparison fields and output

Step 3: Sigma automatically creates the grouping and calculations needed

Why use it? Make your life easier! It’s a faster way to create one of analytics’s most common and fundamental functions. It’s also a great way for new users to learn how to use Sigma’s table aggregation features properly.

Anything else to know? The value calcs created still follow the rules of grouping hierarchies because they are a simple aggregation (sum, avg, etc). But the datelookbacks do not follow the same grouping hierarchy rules because they are “Grand Aggregations” (like the GrandTotal calculation) attached to dates.

This image illustrates how these calculations are/aren’t affected by the grouping hierarchies. You can see the blue circles are the same because the calc is Sum([Price]) vs the blue squares which are DateLookback(Sum([Price]), [Year of Date], 1, “year”)

LMK what you think of these! Would love to hear if you find them helpful

Will this be expanded to include hours and minutes?

This is my new favorite feature!
In the past I have converted the date column to month-day
DateFormat([Record Date Pt], “%m-%d”)
and then used Year([Record Date Pt])
but this is way less complicated

1 Like

Hey @MichalS! Thanks for chiming in. Dillon here, product manager for this function.

We hadn’t discussed minutes or hours yet. Would you mind elaborating on your use-case and I can take this back to the team?

Hey @Dillon,

An example use-case:
I have a report that tracks member registrations to our product.
The report includes columns tracking registrations in the last 10 minutes, last 60 minutes, and last 24 hours.
Each of those columns are additionally broken out to compare with previous time periods. For example, registrations in the last 10 minutes, registrations from the same 10 minutes last week, registrations from the same 10 minutes last month etc.
I’m using some nested dateadd functions to accomplish this for now.
For example: `CountDistinctIf([Memberid], [Registereddate] >= DateAdd("minute", -10, DateAdd("week", -1, Now())) and [Registereddate] <= DateAdd("week", -1, Now()))`
It would be great to be able to accomplish this with the period comparison lookup.

1 Like