Calculate average of a measure over a specific period of time

Q&A
, ,
1

Hi! I have a dataset with a measure and a date dimension. I need to calculate in QuickSight the average of that measure over a specific period of time, specified by parameters Start Date and End Date. I know how to do this using a Visual and a filter, but I need to do it using a calculation. It would be something like:

avg(measure, date, start_date:end_date)

I have tried with PeriodToDateAvg and similar but with those I cannot select a specific period of time. Thanks is advance!

2

Hi @jorge1. Welcome to the Community. :slight_smile: I entered your issue into our Community chat agent and this is what I got. I have asked @Brett to validate this when back online on Monday – but hopefully in the meantime this will help you get unblocked. Have a great weekend!

To calculate the average of a measure over a specific time period, you can use the extract function to filter data within your desired time range. You can create a calculated field that uses the extract function to check if the timestamp falls within your specified hours. 1

For example, you could use a formula like:

ifelse(extract('HH',{date_field})>=start_hour AND extract('HH',{date_field})<=end_hour, {measure}, NULL)

This calculation would only include values where the hour component of your date field falls within your specified range.

For more complex time-based calculations, you might consider using level-aware calculations (LAC) in QuickSight. These allow you to inject calculations at specific points in QuickSight’s evaluation process. 2 3

The PRE_FILTER calculation level could be particularly useful as it evaluates calculations before applying filters from the analysis. 2 3

If you need to work with specific date ranges rather than just time components, you might also find the truncDate function helpful for manipulating date values in your calculations. 4

No answer is found for a direct function with the exact syntax of avg(measure, date, start_date:end_date), but the combination of extract function with conditional logic provides a workable solution for calculating averages over specific time periods.

3

Hi @jorge1,
Following up here to see if you had any additional questions regarding your initial post or the response provided; were you able to find a solution that worked for your case?