By Richard Sharkey – Principal BI Engineer
As you know, here at synvert TCM (formerly 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
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. Modern BI tools 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. It can be referenced 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. It makes dashboards more usable, particularly for those users who are short on time and need their answer straight away.
Summary
Hopefully you found this short blog informative, find out how we can help you with your QuickSight journey here!
