Rolling Average calc for use in line chart

Question & Answer
5

Thats interesting, thanks for sharing. It got me closer but I am not able to execute a rolling calc, Here is where I am.

I started to work on this by creating a query and joining it to my dataset like this -

Screen Shot 2022-09-14 at 11.44.25 AM
Screen Shot 2022-09-14 at 11.44.25 AM1548×432 24.6 KB 

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.

ifelse(count({OM Date}) >= 1, count({OM Date}), 0)

I am getting errors about combining non-aggregated and aggregated calculations when I try and create a rolling calculation. Any ideas?