How to Dynamically Group the Last N Days vs. the Rest of Your Data 📅

Modeling
,
1

Ever wanted a quick, hands-off way to see how your metrics for the last 7 days stack up against everything else? Now you can build dynamic grouping directly into your data model!

Here is a quick step-by-step guide on how to set this up using Durations and Bins.

Step 1: Calculate the Date Difference in the UI

First, we need to determine exactly how old each record is. Jump into the UI and use our Durations function.

image
image514×403 21.3 KB

You’ll want to set this up to get the time difference (in days) between your core timestamp (like a fiscal date, creation date, or order date) and a secondary date.

Note: When doing this in the workbook you’ll need to select an end date field initially before continuing to the next steps

Step 2: Make it Dynamic in the IDE

To make sure we are always comparing against a rolling 7-day window, our end date needs to always be today.

Jump over to the IDE and locate the new duration dimension you just generated from the UI (Hint: Look for Go to definition in the 3 dot menu) All you need to do is edit the sql_end: parameter and set it to CURRENT_DATE* (*Or equivalent SQL syntax).

Your end result will look like this in your IDE:

date_duration:

    label: Date Duration

    duration:

      sql_start: ${viewname.date[date]}

      sql_end: CURRENT_DATE

intervals: [ days ]

Step 3: Group into N-Day Increments Using Bins

Now that we have a dynamic integer representing the age of the record in days (e.g., 2 days ago, 5 days ago, 14 days ago), it’s time to bucket them.

Now that you have the date_duration field you can use the Bin function to group the data by N-day increments.

image
image407×682 30.9 KB

The Result! :rocket:

You’re all set! By pulling this new binned dimension into your query, you’ll immediately see your data cleanly segmented.

For my example, I was grouping by last 7 days with anything beyond that grouped into it’s own bin.

  • The 0-7 bin represents your dynamic trailing 7 days.

  • Any bins greater than 7 (7-14, 14-21, etc.) represent the rest of your historical data outside of that window.

image
image391×267 10 KB