I would like to create a rolling average calculation to use in a line chart. The functions runningAverage and windowAverage do not fit my use case.
I want to create a calculation that averages {field} for rows with {OM Date} <= {date} and {OM Date} >= {date}-30days.
In my dataset, not all days appear in {OM Date}, which is why runningAverageandwindowAverage` do not fit my use case. The closest I have gotten to a solution is creating a date parameter and plugging it into a calculation and x-axis, but this is only one date/day and not a series of dates to plot on the x-axis.
For each day on the x-axis, I want to average a field for rows on that day and those within 30 days before that day.
On August 31, average rows with dates between August 31 and August 1.
On August 30, average rows with dates between August 30 and July 31.
etc …
Not all days appear in the dataset, so I think I need to access a date series/range. I thought the closest I got to a solution was to create a date parameter, but I could only create a single day and not a range.
Not all days appear in the dataset…
You may have to force the dataset to have every day in it by forcing the missing days to have a value of zero (0). Check out this topic where we used a left outer join with the calendar as the primary table and the facts (data) as the secondary table: How to show Zeros in the pivot table?
with calendar as (SELECT generate_series(
MIN(DATE_TRUNC('day', "OM Date")::DATE),
MAX(DATE_TRUNC('day', "OM Date")::DATE),
'1d')::date as day
FROM "nrtp_report"."nrtp_property_report"
),
metros as (
select distinct "MSA"
FROM "nrtp_report"."nrtp_property_report"
)
SELECT * FROM calendar
CROSS JOIN metros
I did a cross join because I want to look at a rolling average by MSA too. I could not pass a multiselect parameter for MSA to use it to a calculation to view stats by metro.
This calculation works to get a count for each day.