Setting a Dynamic Date filter in QuickSight (AWS)
hi there, wanted to share a quick tip in creating a dynamic date filter in QuickSight.
What is the requirement?
The need for this originates from a requirement that we sometimes need to show the data for the latest date. This latest date should not be the current date, reason being that if there is no data available for the current calendar date, then the visual will fail.
Here, the user may have interest on the visual which is available for the latest data available in the dataset. This requires to have a dynamic date filter which is data based.
How to set up the dynamic filter in AWS QuickSight
Pretty easy! Open the dataset on QuickSight console and click on Add Calculated Field: Create the new filed called “maxdatets” and use the following formula. Click Save and Publish the dataset. This variable maxdatets in our calculated field which will be used as a dynamic filter.
Now, open the analysis in QuickSight where you want to use this filter. One useful place to use this is in a Gauge Chart.
In the gauge chart, select a measurement on which Gauge Chart needs to be created. Once the Gauge Chart is made, set up the filter as following.
Open the filter pane, click on “+” sign, and select a field datets. This field should be the same field on which you have created the maxdatets variable above and provide the following information into the dialogue box available.
Click Apply and your filter is ready and graph is updated as desired.
Above is the impact of applying the maxdatets filter on above Gauge Chart. This is similar to writing a sql query where data is filtered based on max clause on a field.
Happy Visualizing.