Dynamic date hierarchy selection

Use Case for Dynamic Date Hierarchies

In QuickSight, analysts (authors) can create dashboards with multiple visuals, which are consumed by QuickSight end users (readers). Readers can interact with dashboards by applying filters, drill-down/roll-up data or invoke custom actions. But unlike authors, readers have limited ability to customize attributes within visuals. Authors have the ability to provide readers with further controls to change visualizations beyond simple filtering and drill-down/roll-up. For some use cases, customizing these controls will be necessary to achieve the desires functionality. One example where this may be required is in displaying date based aggregations.

Although QuickSight provides easy out-of-box methods to drill-down into standard date hierarchies for datetime datatypes (Year→Quarter→Month→Week→Day→Hour→Minute→Second) by readers, in many scenarios readers may want to analyze measures in a visual over a custom date hierarchy. For example, they may want to analyze measures over a custom date hierarchy (Year, Month, Day) or they may want to analyze measures over fiscal dates (FiscalYear, FiscalQuarter, FiscalMonth) rather than standard calendar dates. Furthermore, readers may want a more scalable way of traversing the various date dimensions.

The following article explains how to dynamically swap out dimensions by using a control and parameter.
A QuickSight reader can analyze “sum of sales” over OrderDate based on a user selected level in a date hierarchy (Year, Quarter or Month) via drop-down control.

Sample Dataset

The sample dataset used in this blog is available at QuickSight author workshop (Workshop Studio). You can download it using this link (https://ee-assets-prod-us-east-1.s3.amazonaws.com/modules/337d5d05acc64a6fa37bcba6b921071c/v1/SaaS-Sales.csv). In this dataset the measure “sales” can be calculated across various dimensions: Segment, Industry, Region, Subregion, OrderDate.

Prerequisites to Bring Your Own Dataset

You may also use your own dataset too while following along with the instructions below. In order for this to work however, you will need to ensure your dataset has a date field included with the dataset. Along with one date field, your dataset must also include a measure value. In this blog we will be building bar chart for measure value in data against date dimension.

Visual Design

We will build a single bar chart visual analyzing sales over by OrderDate. On the X-axis we want to plot OrderDate and on Y-axis we plot sum(sales).

In this bar chart a QuickSight reader may want to analyze sales across different aggregation levels of an OrderDate hierarchy (Year, Month and Day), and they want to display a different date format for each level (e.g. YYYY for years, QQ-YYYY for quarters, MMM-YYYY for months, etc).

Using a drop-down control the QuickSight reader can dynamically select the level of OrderDate in Year, Quarter or Month. Based on this control we will set a parameter value. This parameter value is used in a calculated field of QuickSight. This calculated field is dynamically aggregated/computed on the chosen date level. Rather than using OrderDate directly we will be using this calculated field on the X-axis in the bar chart to render sum(sales) at different OrderDate levels.

Because we want to display a different format for each level, our calculated field will output a String, not a Date field, which also means we will want to ensure those strings are sorted properly. To achieve this, we will create an integer field called “MyOrderDateKeySort” and use it the sort for the visual.

Now let’s dive deep into the actual steps of building it in next section.

Steps to build the dashboard

Step 1: As a QuickSight author create new dataset from the CSV file downloaded from this link (https://ee-assets-prod-us-east-1.s3.amazonaws.com/modules/337d5d05acc64a6fa37bcba6b921071c/v1/SaaS-Sales.csv).
Navigate to Datasets.
Click “New Dataset”
Click “Upload File”
Choose “SaaS-Sales.csv”
Click “Next” and then “Visualize”
01_CreateDataset

Step 2: Create an analysis with a bar chart visual
In the visualization, select the visualization and choose “Vertical Bar Chart” from the visual types section.
Add the field “Order Date” to the x axis and “Sales” to the value in the “field wells”.
Click the drop down on the x axis and change the aggregate to “Year”.
02_BarChartVisual

Step 3: Create calculated field for different date levels
Create three calculated fields to represent the Year, Quarter and Month parts of the OrderDate field. Later we will be using them represent the chose granularity on the X-axis.

OrderDateYear:
toString(extract('YYYY', {Order Date}))
03_DateCalc split_YearCalc

OrderDateQuarter:
concat('Q',toString(extract('Q',{Order Date})),'-',toString(extract('YYYY',{Order Date})))
03_DateCalc split-QuarterCalc

OrderDateMonth:
concat(left(formatDate({Order Date},'MMM-dd-yyyy'),3),'-',toString(extract('YYYY',{Order Date})))
03_DateCalc split-MonthCalc

Step 4: Create sort key

Add a new calculated field of type integer for sortKey “MyOrderDateKeySort” using orderDate. We will be using this calculated field to sort the visual.

MyOrderDateKeySort:
parseInt(concat(toString(extract('YYYY',{Order Date})),toString(extract('MM',{Order Date})),toString(extract('DD',{Order Date}))))

04DateKey

Step 5: Create the control and parameter to select date dimension hierarchy

Create a parameter “pDateGrain” for use in the control.

In the parameter set the default value to “Year“. In the control options define the specific values as Year, Quarter, Month.

05ParameterControl

Step 6: Create a calculated field for the dynamic selection of OrderDate level

Add a new calculated field called DynamicDateDim.
DynamicDateDim: ifelse(${pDateGrain}='YEAR',OrderDateYear,${pDateGrain}='Quarter',OrderDateQuarter,${pDateGrain}='Month',OrderDateMonth,OrderDateYear)
06_Dynamic_Dim_calcField

Step 7: Add the OrderDate level calculated field in the field well and edit axis label text to match the selected value from the drop-down control.

07DynamicDateDimandLabel

Final test as QuickSight reader

Publish your Dashboard and test your result for different aggregation levels of date dimension as a QuickSight reader.

08_DashboardPublishandTest

Summary

We have demonstrated how you can create calculated fields to extract the different grains Year, Quarter and Month from a date datatype. We have conditionally used these date grains in a visualization based on various level selections in a date hierarchy via drop-down control. The same method can be leveraged for use cases where you may want to calculate common measures and swap different dimension values e.g. in single bar chart. You may want to calculate sum(sales) over three different dimensions say Region,Segment,Industry. You may use Step-6 and Step-7 in this Blog to swap dimensions in the X-axis based on the selection via QuickSight drop-down/radio control. We will demonstrate it in part 2 of this Blog series.

Authors, contributors and reviewers

Anwar Ali, Senior QuickSight Specialist at AWS
Douglas Berquist, Manager QuickSight Specialist at AWS
Ashok Dasineni, Senior QuickSight Specialist at AWS
Dan Shallenberger, Senior Solution Architect, AWS Public Sector and EducationTech