Amazon QuickSight – periodToDatePercentileCont

The periodToDatePercentileCont() function in Amazon QuickSight is a statistical function that calculates the percentile value for a measure over a specified period of time. It uses continuous percentile calculation, which is more precise than discrete percentile calculation, particularly when you are working with large datasets.

Syntax

#Start#
periodToDatePercentileCont(
    measure, 
    percentile, 
    dateTime, 
    period, 
    endDate (optional)
)
#End#

This function takes the following arguments:

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

percentile: This parameter specifies the percentile that you want to calculate. The valid values for the percentile parameter are between 0 and 1, inclusive.

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

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

endDate (optional): This parameter specifies the end date and time of the time period. If this parameter is not specified, the current date and time will be used as the end date.

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

Example

#Start#
periodToDatePercentileCont(Sales, 0.9, Date, 'day', now(), Region)
#End#

This formula calculates the 90th percentile of the Sales field for the current day, grouped by Region. The now() function is used to specify the current date and time as the end date. This function can help you identify the highest sales values for the day in each region and compare them to the 90th percentile of the data, with a higher degree of precision than the periodToDatePercentile() function.