Add comparative and cumulative date/time calculations in Amazon QuickSight

Amazon QuickSight lately added native assist for comparative (e.g., year-over-year) and cumulative (e.g., year-to-date) interval features which let you simply introduce these calculations in enterprise reporting, development evaluation and time collection evaluation. This enables authors in QuickSight to implement superior calculations with out having to make use of sophisticated date offsets in calculations to attain such datetime-aware comparisons.

On this publish, we introduce the brand new interval features and their capabilities, and reveal a number of typical use instances. We additionally focus on a number of eventualities to increase the utilization of the interval features, which might be helpful in additional superior conditions.

New interval features

Earlier than we reveal use instances, let’s go over the brand new interval perform suite and see what new features we now assist. We will divide interval features into two most important teams: comparative (interval over interval) features and cumulative (interval to this point) features.

Comparative (interval over interval) features

You should utilize interval over interval features to check measures at totally different time durations, akin to 12 months, quarter, and month. For instance, you’ll be able to compute a year-over-year enhance in gross sales, or week-over-week proportion income modifications.

A typical comparative interval perform has the syntax periodOverPeriodDifference(measure, date, interval, offset), with two elective arguments: interval and offset.

You should utilize the interval argument within the perform to outline the interval granularity of the calculation. The granularity of YEAR means year-over-year computation, Quarter means quarter-over-quarter, and so forth. If the interval argument is left empty, the calculation modifications primarily based on the interval granularity that’s chosen (within the subject effectively) to be displayed within the visible.

You can even use the offset argument to specify what number of durations aside you wish to compute the comparability. As an illustration, a interval of 1 / 4 with an offset of 2 means evaluating towards the earlier two quarters.

Notice that interval and offset must be each specified or each left empty. You possibly can’t specify simply certainly one of them.

The next desk summarizes the three accessible interval over interval features.

Cumulative (interval to this point) features

You should utilize interval to this point features to calculate metrics inside a given period-to-date window. There are two most important forms of cumulative features.

• “OverTime” features, that are desk calculations and return outputs for every row within the visible.

E.g., you need to use periodToDateCountOverTime with WEEK granularity to compute a collection of week-to-date new buyer counts to trace the fluctuation of buyer engagement.

• Aggregation features, which output an aggregated worth for a set period-to-date time vary.

E.g., periodtoDateSum with YEAR granularity returns a single worth for the whole of the metric from the start of the 12 months to the endDate supplied within the formulation. If left clean, the default endDate takes now(), which is the second when customers load the dashboard.

The next desk summarizes the interval to this point features.

Use case 1: Utilizing a interval over interval perform to research gross sales progress in mounted and dynamic granularity

Let’s dive into how interval over interval features can assist typical enterprise and monetary use instances. The next instance makes use of periodOverPeriodDifference to calculate YoY gross sales progress. As a result of we have now Section and Area within the visible, the YoY gross sales is calculated for every phase and area.

We outline the measure of YoYSales with the next formulation: YoYSales=periodOverPeriodDifference(sum(Gross sales),{Order Date},YEAR,1)

The primary argument, sum(Gross sales), tells the perform to calculate primarily based on this measure. The second argument, Order Date, specifies the date/time column from which Yr data is extracted. The third argument, YEAR, fixes the granularity of this calculation. When this elective argument is specified, this measure at all times returns YoY (not QoQ or MoM) regardless of how Order Date is chosen (within the subject effectively) to be displayed within the visible. The fourth argument, 1, specifies the offset of the comparability. On this instance, it means we wish to evaluate the gross sales of every order date with the identical date of the earlier 12 months. The measure returns empty for order dates of 2018, as a result of no earlier durations exist to be in contrast with.

The interval features are working with totals and subtotals. By including the whole for columns into the visible, you’ll be able to see the whole gross sales and whole YoYSales for every area.

In the event you depart the elective argument of interval granularity empty, which means change the formulation to PoPSales=periodOverPeriodDifference(sum(Gross sales),{Order Date})as proven within the following instance, the time interval of the calculation is then decided by the granularity of Order Date displayed on the visible. Within the following instance, Order Date is chosen to show at quarter degree (within the subject effectively), so PoPSales dynamically calculates the QoQ gross sales progress. Altering Order Date to the month-to-month degree updates the measure to calculate MoM. For PoPSales, solely Q1 2018 returns empty as a result of that’s the one quarter that doesn’t have a earlier quarter to check with.

If we add YoYSales from the earlier instance to this visible, it calculates YoY gross sales progress on the quarter degree (compares gross sales of Q1 2019 with Q1 2018). This demonstrates the distinction between a set granularity and a dynamic granularity of interval over interval features.

The interval over interval features can differentiate between a constructive change (enhance) and damaging change (lower). Due to this fact, after we add the conditional formatting to the visible, it’s very easy to see the monetary efficiency of every interval (inexperienced is nice, pink is unhealthy).

Equally, you need to use periodOverPeriodPercentDifference to calculate relative gross sales progress over time. You possibly can add dimensions into the visible to dive additional into enterprise insights, akin to analyzing the breakdown of every enterprise phase’s gross sales change by quarter, and their contribution to the whole gross sales enhance. We use the formulation PoPSales%=periodOverPeriodPercentDifference(sum(Gross sales),{Order Date}).

Use case 2: Utilizing a interval to this point perform to trace YTD gross sales in desk calculations and aggregations

Much like interval over interval features, the interval to this point perform suite offers a fast and straightforward method to calculate year-to-date (YTD) or quarter-to-date (QTD) metrics. Within the following instance, we use the formulation of YTDSumoverSales=periodToDateSumOverTime(sum(Gross sales),{Order Date},YEAR), and YTDtotalcountSales=periodToDateSumOverTime(rely(Gross sales),{Order Date},YEAR) to calculate YTD gross sales and YTD whole variety of transactions.

Opposite to interval over interval features, the third argument of interval to this point features, interval, isn’t elective. Due to this fact, the calculation granularity is at all times mounted. On this instance, with the granularity outlined as YEAR, this measure at all times calculates YTD, as a substitute of QTD or MTD. As a result of Order Date is displayed on the month-to-month degree, this calculation outputs the YTD gross sales of every month, and begins over once more in January for the subsequent 12 months. As proven within the end result desk, YTDSumoverSales of January 2018 is the month-to-month gross sales of January 2018, and YTDSumoverSales of February 2018 is the month-to-month gross sales of January 2018 plus that of February 2018. And YTDSumoverSales of January 2019 goes again to the month-to-month gross sales of January 2019.

You possibly can additional dive into the small print by populating the calculations in a line chart, and including extra dimensions into the evaluation. The next instance exhibits the YTD weekly gross sales progress development for every area alongside the previous 4 years, and uncovers some fascinating gross sales competitors between AMER and EMEA in 12 months 2021.

Along with the desk calculations, the aggregation interval features are significantly helpful when you must construct KPI charts to guage YTD metrics in a real-time method. Within the following instance, we use the aggregation interval to this point features to construct two KPI charts to trace the YTD whole gross sales, and YTD whole variety of transactions. For the date December 26, 2021, the timestamp outcomes match the corresponding desk calculations for the date of December 26, 2021 within the desk. The next desk summarizes the formulation.

Superior use case 1: Date/time consciousness with interval features

Interval features aren’t solely simpler to outline and browse, they’re additionally date/time-aware, which means the features are calculated primarily based on a date/time-based offset as a substitute of a set variety of rows. It may well resolve two main issues that weren’t doable to be addressed earlier than.

Interval features can deal with various interval period

If you wish to calculate the every day MoM gross sales enhance, you’ll be able to’t use a set offset on every month as a result of the variety of days of every month are totally different (31 days for January and 28 or 29 days for February).

Interval features are calculated primarily based on calendar dates as a substitute of a set offset. Within the following instance, we use the formulation MoMsalesDiff=periodOverPeriodDifference(sum(Gross sales),{Order Date},MONTH,1). The every day MoM enhance is calculated accurately primarily based on the day of the month. The gross sales of the primary day of the month are in contrast with the primary day of the earlier month, and the identical applies to all different days. (Visuals are duplicated for demonstration functions.)

Interval features can deal with sparse (lacking) knowledge factors

Not all datasets can assure a whole set of dates. Within the earlier instance, gross sales knowledge of January 1, 2018, is lacking. Utilizing the workaround primarily based on a set offset may cause an issue right here as a result of we evaluate February 1, 2018, with a special date as a substitute of January 1, 2018. Interval features at all times evaluate measures by date/time offsets in order that solely desired dates are in contrast. Within the earlier instance, MoMsalesDiff exhibits empty for February 1, 2018, due to the lacking knowledge of January 1, 2018.

Superior use case 2: Nesting interval features with different calculations

Now that we are able to use interval over interval and interval to this point features to create calculated fields, we are able to nest these features with different calculations to drive extra superior evaluation.

For instance, you might wish to know for annually, what are the highest 10 weeks of the 12 months with reference to week-over-week gross sales progress. You are able to do this by calculating WoWSales first: WoWSales=periodOverPeriodDifference(sum(Gross sales), {Order Date}, WEEK, 1). You then nest it with the denseRank window perform: RankWoWSales=denseRank([WoWSales DESC],[{YEAR}]). This wouldn’t be doable utilizing the fixed-row primarily based workaround, which is carried out utilizing on-visual calculations as a substitute of calculated fields. Within the following visible, the highest 10 weeks of every 12 months with the best gross sales progress are fetched by a easy filter on RankWoWSales.

You possibly can even nest the interval features with different interval features to generate fascinating insights. For instance, you’ll be able to calculate a month-to-month YoY progress primarily based on the month-to-month YTD variety of transactions. The next formulation demonstrates the aptitude of nesting a YTD calculated subject inside a YoY calculated subject:

YTDtotalsountSales=periodToDateSumOverTime(rely(Gross sales),{Order Date},YEAR)
YoYYTDSalesCount=periodOverPeriodDifference(YTDtotalcountSales,{Order Date},YEAR,1)

The ends in the next visible present a YoY progress primarily based on a YTD collected variety of transactions as a substitute of absolutely the month-to-month numbers.

Superior use case 3: Partial interval comparisons

Lastly, we focus on a 3rd superior use case: partial interval comparability. Think about it’s November 15, 2021 (which is the forty sixth day of the final quarter of 2021), and also you wish to calculate 4 to check the efficiency of this quarter with previous quarters, however solely utilizing the primary 46 days of every quarter as a substitute of the entire quarter. This requires a calculated subject utilizing periodOverPeriodDifference nested with the sumIf() window perform.

The next instance demonstrates utilizing a nested calculated subject to deal with this use case:

QuarterToDate=periodToDateSumOverTime(sum(Gross sales), {Order Date}, QUARTER)
PartialQTDSales=sumIf(Gross sales, dateDiff(truncDate("Q", {Order Date}), {Order Date}, "HH") <= dateDiff(truncDate("Q", now()), now(), "HH"))
PartialQoQQTDSales=periodOverPeriodDifference(sumif(Gross sales, {Order Date} <= addDateTime(dateDiff(truncDate("Q", now()), now(), "HH"), "HH", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

PartialQTDSales computes what number of hours from the start of this quarter to the present date and makes use of sumIf() to calculate the whole gross sales of that interval of every quarter. partialQoQQTDSales then nests the periodOverPeriodDifference perform with PartialQTDSales to seek out the partial QoQ variations. Such a comparability primarily based on a partial interval isn’t possible with out the brand new date/time-aware interval features.

Conclusion

On this weblog, we launched new QuickSight interval features which allow fast and highly effective date/time-based calculations. We reviewed comparative and cumulative interval features (i.e., interval over interval and interval to this point), mentioned two main use instances (mounted vs. dynamic granularity and desk calculation vs. aggregation), and prolonged the utilization to 3 superior use instances. Interval features are actually typically accessible in all supported QuickSight Areas.

Trying ahead to your suggestions and tales on the way you apply these calculations for your enterprise wants.

Concerning the Authors

Emily Zhu is a Senior Product Supervisor at Amazon QuickSight, AWS’s cloud-native, totally managed SaaS BI service. She leads the event of QuickSight core analytics and calculations functionality. Earlier than becoming a member of AWS, she was working within the Amazon Prime Air drone supply program and the Boeing firm as senior strategist for a number of years. Emily is keen about potentials of cloud-based BI options and appears ahead to serving to clients to advance of their data-driven technique making.

Rajkumar Haridoss is a Senior Software program Growth Engineer for AWS QuickSight. He’s the lead engineer on the Question Technology staff and works on back-end calculations, question planning, and question technology layer in QuickSight. Exterior of labor, he likes spending high quality time with household and 4-year-old.