Amazon QuickSight – periodToDateCount
The periodToDateCount function in Amazon QuickSight is used to calculate a count of the number of times a given measure occurred within a specified time period. It returns a count of the number of times the measure occurs between the specified start date and the specified end date.
Syntax
#Start# periodToDateCount(measure, dateTime, period, endDate) #End#
This function takes the following arguments:
- measure: The measure to count. This could be a field in the dataset, an aggregate function, or a combination of both.
- dateTime: The date field to use for aggregating the count.
- period: The time period to use for aggregating the count. This could be “day”, “week”, “month”, “quarter”, or “year”.
- endDate: (Optional) The end date of the period. If not specified, the current date will be used as the end date.
Here’s an example that illustrates how to use the periodToDateCount function in QuickSight:
Suppose you have a dataset that contains sales data for a retail store, and you want to calculate the number of days between each sale and the start of the current month. You can use the following expression:
Example
#Start# periodToDateCount(Sales, OrderDate, "day") #End#
In this expression, Sales is the name of the measure that you want to calculate the period-to-date count for, OrderDate is the date/time field in the dataset that contains the order date for each sale, and “day” is the period that you want to calculate the count for.
If you want to calculate the period-to-date count up to a specific end date, you can include the endDate parameter in the function. For example, to calculate the number of weeks between each sale and the start of the current quarter, you can use the following expression:
Example
#Start# periodToDateCount(Sales, OrderDate, "week", "2023-03-31") #End#
In this expression, “2023-03-31” is the end date of the current quarter.