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”

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”.

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.

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

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

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.

toString(extract(‘YYYY’, {Order Date})),
strlen(toString(extract(‘MM’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘MM’, {Order Date}))),
toString(extract(‘MM’, {Order Date}))
strlen(toString(extract(‘DD’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘DD’, {Order Date}))),
toString(extract(‘DD’, {Order Date}))


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.


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)

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.


Final test as QuickSight reader

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



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

Excellent article to learn. Thx for that. I successfully could adapt and use it with my own dataset.
Actually the sorting part doesn’t seem to work correctly in the sample as in my own visuals. The sorting seems to break with the two digit month (correct sorting until Sept, but Nov-Dec will not). Anyone an idea how to fix this?

The issue is with the formula “MyOrderDateKeySort”:
parseInt(concat(toString(extract(‘YYYY’,{Order Date})),toString(extract(‘MM’,{Order Date})),toString(extract(‘DD’,{Order Date}))))

The parameters
extract(‘MM’,{Order Date}) and
extract(‘DD’,{Order Date})
return single digit results for values less than 10.

For example, 4 January 2023, gets converted to 202314 instead of 20230104.
The result is that all Q4 dates, regardless of year, have a higher value than dates in Q1, Q2 or Q3.

I have tweaked the formula as follows:
toString(extract(‘YYYY’, {Order Date})),
strlen(toString(extract(‘MM’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘MM’, {Order Date}))),
toString(extract(‘MM’, {Order Date}))
strlen(toString(extract(‘DD’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘DD’, {Order Date}))),
toString(extract(‘DD’, {Order Date}))

Now, the ordering is correct.

1 Like

Thanks for the update. The date by quarters are sorting correctly now, but as a primary sorting key. The primary sorting shall be by year, the secondary by quarter.
How to tweak the formula to sort this correctly?

Result with above formula
Q1 2021 Q1 2022 Q2 2021 Q2 2022 Q3 2021 Q3 2022 Q4 2021 Q4 2022

intended result
Q1 2021 Q2 2021 Q3 2021 Q4 2021 Q1 2022 Q2 2022 Q3 2022 Q4 2022

@Neil just an addon to my comment above. The sorting works perfectly using “month” as granularity. Thx for that. It doesn’t when using “quarters” as granularity. But don’t bother. I just started getting around and I do understand now the possibilities and the need of getting deeper into marco language programming.

1 Like

Not sure what you mean by primary and secondary sorting. Using “MyOrderDateKeySort” as described in the above article works for each of the date granularities.

For your particular case, perhaps you could change the definition of “OrderDateQuarter” to the following?

concat(toString(extract(‘YYYY’, {Order Date})), ‘-’, ‘Q’, toString(extract(‘Q’, {Order Date})))

Placing the “Q” value after the year value allows correct sorting using the date value itself.

1 Like

Hi @Neil, Thanks for sharing correction of formula.

We have updated the formula in the original post above, with leading “0” for correct sorting.

Anwar Ali

1 Like