Including missing dates for rolling 7 day average

SDT — Thu, 26 Sep 2024 14:46:08 GMT

I have a data set with a complete calendar (includes every date). I also have a table for orders and there will not be an order on every date. Each orders has several dates associated with it (order date, scheduled date, ship date) so there is a link table between the order table and the date table.

I have a line chart with the x-axis being the date from the complete calendar. The measure has set analysis to only include orders and only use the order date from the link table.

I want to show a 7day average (including all calendar days) and used RANGEAVG(ABOVE(expr,0,7)). The problem is not all dates are included because there are some days with zero orders based on the set analysis.

Is there a way to accomplish a true 7 day rolling average?

Re: Including missing dates for rolling 7 day average

Kushal_Chawda — Thu, 26 Sep 2024 15:23:19 GMT

@SDT try below expression

RANGEAVG(ABOVE(expr + sum({1}0),0,7)).

Re: Including missing dates for rolling 7 day average

SDT — Thu, 26 Sep 2024 17:43:04 GMT

@Kushal_Chawda Thank you very much!

topic Re: Including missing dates for rolling 7 day average in App Development

Including missing dates for rolling 7 day average

Re: Including missing dates for rolling 7 day average

Re: Including missing dates for rolling 7 day average