QuickSight Relative Date Defaults Are Here!

By Richard Sharkey – Principal BI Engineer

As you know, here at Crimson Macaw we’re big fans of QuickSight…and AWS have snuck in a new update for Parameters, and we are really happy about this one – Relative Date Defaults Are Here!

Filters vs Parameters

Sometimes a dashboard just needs a date filter. Maybe it’s “current month”, “previous day” or “last 12 weeks” and filtering that information for all the visualisations makes sense. You can make the filter an available prompt for a user to change as part of the dashboard functionality and the requirement is met.

Sometimes a visualisation in your dashboard may have a mixed time context.

Take the example specification below.

This requires 6 measures to be calculated for:

Day-TY – The daily performance this year

Day-LY – The daily performance last year

Day-LY Var % – The % variance to the same day the previous year

MTD-TY – The MTD performance this year

MTD-LY – The MTD performance for the equivalent period last year

MTD-LY Var % – The % variance to the equivalent period last year

QS Example Spec

This is where single date filters begin to struggle. You need the user to be able to select a “date” that the above calculations will anchor to for each individual time context. In modern BI tools that have traditionally lacked the semantic capability of time series intelligence to the extent you would find with Oracle BI and SAP Business Objects, parameters have been the solution for this type of requirement. A parameter can be referenced by multiple calculations with differing logic.

Example

MTD-TY

ifelse(OrderDate>=truncDate('MM', ${ParamDate}) and OrderDate <=${ParamDate},  Sales,0)

This example only returns a value when the date range falls between the start of the month for the parameter value and the parameter value itself.

Limitation of Parameters

This mechanism works great and when the user selects a different date, all the calculations will shift to return the correct time context answer.

However, a traditional problem with Parameters in the Modern BI tools, is the ability to set a dynamic default for a date Parameter. A very reasonable requirement that users have is that when they login to look at a daily dashboard that the date Parameter “defaults to yesterday” so they don’t need multiple clicks to get the information they need.

Up until now, the way to achieve this with QuickSight was to leverage User Dynamic Defaults which essentially requires you to create a view on the database with every user and then add a relative date field to the view that always returns yesterday’s date. You can then add that to QuickSight as a separate dataset and reference it as part of the dynamic defaults functionality within a parameter.

This works fine, but it’s a chunky workaround for what is seemingly a simple requirement! It also carries the burden of maintenance; you must remember to add the users to the table every time a new user is added.

Relative Defaults

Relative defaults now make this requirement so much simpler to implement. You now get an option to select a relative date as the default when you create a parameter. This is a huge time and maintenance saver and makes dashboards more usable, particularly for those users who are short on time and need their answer straight away.

QS Relative Default

Summary

Hopefully you found this short blog informative, find out how we can help you with your QuickSight journey here!