Amazon QuickSight – periodToDateSum

The periodToDateSum() function in Amazon QuickSight is an aggregation function that calculates the sum of a measure for a given time period, up to a specified end date. This function is useful when you want to analyze the total value of a measure over time and compare it to the overall value of the data.

Syntax

#Start#
periodToDateSum(
    measure, 
    dateTime, 
    period, 
    endDate
)
#End#

This function takes the following arguments:

measure: This parameter specifies the measure for which you want to calculate the sum. The measure can be a field or an expression.

dateTime: This parameter specifies the date and time for which you want to calculate the sum. The parameter should be in the timestamp format.

period: This parameter specifies the time period for which you want to calculate the sum. The valid values for the period parameter are hour, day, week, month, quarter, and year.

endDate: This parameter specifies the end date and time of the time period for which you want to calculate the sum. The parameter should be in the timestamp format.

Suppose you have a sales dataset with the following fields: Date, Product, Sales, and Region. You can use the periodToDateSum() function to calculate the sum of sales for the current week by region. Here’s an example formula:

Example

#Start#
periodToDateSum(Sales, Date, 'week', now())
#End#

This formula calculates the sum of the Sales field for the current week, up to the current date and time. This function can help you analyze the total value of sales for the week and compare it to the overall value of the data. You can further group the results by Region to see the total sales value for the week in each region.