How to Make Simple, User-Friendly Date Filters

[kaela]

Sigma’s date filtering is incredibly robust. It lets you and your users do all sorts of date gymnastics with just a few clicks.

But sometimes, and especially if you’re providing a clean, crisp product to your users, you might want to simply offer a few easy-to-click date ranges to pick from on your dashboard. Things like “Last Month”, “Last 3 Months”, “Last 6 Months”, and “Last 12 Months”.

Instead of using Sigma’s native Date filtering, we’re going to achieve simple date range selection by making a List Element and plugging in the date ranges we want to offer.

Then, we’ll filter our source tables or visualizations based on that, and let the user choose the date range they want to see.

Here are the steps:

1. Create a List Element containing date range options (as strings).
2. Create a T/F column in your source table or viz that tells Sigma which dates to keep for each List value.
3. Create a filter on your source table or viz that only keeps True, and select True.
4. OPTIONAL: Add dynamic text somewhere that shows the MIN(Date) and the MAX(Date) so that people know the exact time period that they’re seeing.

STEP 1:

Create a New List Element. Set it up to include the text you want to display to your users. In this case I chose a series of “Last __ Months” options, but you can do this with days, quarters, years, etc. Your new List Element will automatically get added to your dashboard interface.

I named my List “Date-Range-Control” at the bottom of the List menu. You’ll want to use this in your calculation in the next step, so name it whatever is easy for you to remember.

Here is what the List will look like on your Dashboard. You can always make shorter names for the values, and set the Display values to be more user-friendly.

STEP 2:

Create a New Column in your source table or viz that tells Sigma which dates to keep for each item in your List. This will create a True/False column in your source table or viz. In my image below, I highlighted the Trues green.

There are two ways to do this based on your needs: using Exact Dates (“last month” = last 30 days), and using Complete Time Periods (“last month” = the prior full month). It’s up to you which one is more appropriate for your users’ needs.

Here are example calculations for each, using the date options in my example.
***NOTE: Replace [Date] with whatever your own date column name is.

EXACT DATE RANGES - Use DateAdd() and Today():

Switch([Date-Range-Control], "Last 12 Months", Between([Date], DateAdd("year", -1, Today()), Today()), "Last 6 Months", Between([Date], DateAdd("month", -6, Today()), Today()), "Last 3 Months", Between([Date], DateAdd("month", -3, Today()), Today()), "Last 1 Month", Between([Date], DateAdd("month", -1, Today()), Today()), "Last 7 Days", Between([Date], DateAdd("day", -7, Today()), Today()))

FULL MONTHS - Use InDateRange():

Switch([Date-Range-Control], "Last 12 Months", InDateRange([Date], "last", "month", 12), "Last 6 Months", InDateRange([Date], "last", "month", 6), "Last 3 Months", InDateRange([Date], "last", "month", 3), "Last 1 Month", InDateRange([Date], "last", "month", 1), "Last 7 Days", [Date] <= Today() and InDateRange([Date], "last", "day", 7))

Adjust those calculations to fit your needs for DateParts (years, months, weeks, days) or for if it’s relative to Today or Yesterday.

STEP 3:

Create a filter on your source table or viz that only keeps True, and select True.

STEP 4 (OPTIONAL BUT RECOMMENDED):

Users want to be really sure that they’re looking at the right data. So I like to add dynamic text somewhere that shows the MIN(Date) and the MAX(Date) so that people know the exact time period that they’re using.

Create a new Text Element. Use dynamic values (Type ‘=’ and then your reference) to show the MIN(Date) through MAX(Date) that are included in your table or chart. This helps users be confident they’re looking at the time period they’re expecting, and can help provide context when sharing images of dashboards. I highly recommend that you do this wherever it makes sense and your users see it!

Here are two examples:

Next to the Filter itself as small, contextual information

Above your chart so it looks like a “title”

That’s it - now you have a nicely formatted, easy-to-use date selector for your dashboard!

Good luck and have fun creating beautiful things in Sigma

[data_katrina]

Love it @kaela! Sometimes it’s better to limit the number of options for end users. And in this scenario, you could filter out anything greater than 12 months potentially increasing dashboard speed.

Love the date labels too. “Last month” might seem clear, but there’s always someone who’s thinking differently.