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.
OrderDateYear:
toString(extract('YYYY', {Order Date}))
OrderDateQuarter:
concat('Q',toString(extract('Q',{Order Date})),'-',toString(extract('YYYY',{Order Date})))
OrderDateMonth:
concat(left(formatDate({Order Date},'MMM-dd-yyyy'),3),'-',toString(extract('YYYY',{Order Date})))
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})),
ifelse(
strlen(toString(extract(âMMâ, {Order Date}))) = 1,
concat(â0â, toString(extract(âMMâ, {Order Date}))),
toString(extract(âMMâ, {Order Date}))
),
ifelse(
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.
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