/    /  Amazon QuickSight – periodToDateAvg

Amazon QuickSight – periodToDateAvg

 

The periodToDateAvg() function in Amazon QuickSight is used to compute the average value of a measure over a specified period, up to the current date or an optional end date. Here is an explanation of the periodToDateAvg() function:

Syntax

#Start#
periodToDateAvg(measure, dateTime, period, [endDate])
#End#

 

This function takes one argument:

 

  • measure: This parameter specifies the measure to be averaged over the specified period.
  • dateTime: This parameter specifies the date/time field to use as the basis for the period. This should be a date or datetime field.
  • period: This parameter specifies the period to be used for averaging. The allowed values for this parameter are: ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’.
  • [endDate]: This parameter is optional and specifies the end date for the period. If it is not specified, the period ends at the current date.

 

 

Suppose we have a dataset that contains information about daily sales revenue for a company and we want to compute the average daily sales revenue for the current quarter. We can use the periodToDateAvg() function in Amazon QuickSight to do this.

 

To compute the average daily sales revenue for the current quarter, we can create a new visual in Amazon QuickSight and add a value field with the following settings:

 

  • Drag the “Sales Revenue” measure to the Values section.
  • Click on the drop-down arrow next to “Sales Revenue” and select “Period-to-Date Average”.
  • In the “Date/Time Field” field, select the date or timestamp field that represents the sale date.
  • In the “Period” field, select “Quarter”.
  • Leave the “End Date” field blank.

This will result in a value that shows the average daily sales revenue for the current quarter.

 

Alternatively, we can create a calculated field in Amazon QuickSight with the following expression

 

Example

#Start#
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter')
#End#

 

This expression will compute the average daily sales revenue for the current quarter.

 

For instance, if our sales data is as follows:

Sale DateSales Revenue
2022-01-0110000
2022-01-0220000
2022-01-0315000
2022-01-0418000
2022-01-0512000
2022-01-069000
……
2022-03-3017000
2022-03-3122000

 

Then, the average daily sales revenue for the current quarter will be:

Example

 

#Start#
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter') = (total sales revenue for current quarter) / (number of days in current quarter)
total sales revenue for current quarter = sales revenue for 2022-01-01 to 2022-03-31 = 1020000
number of days in current quarter = 90
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter') = 11333.33
#End#

In this example, we can see that the periodToDateAvg() function has computed a useful metric that can inform our business decisions. Note that we can also specify an end date to compute the period-to-date average up to a