Moving average with non consecutive data

Mitubhai — Wed, 16 Oct 2019 17:32:21 GMT

Hello everyone,,

I would like to calculate the moving average of counts of my data.

My data describes each time an asset has been inspected/repaired by its AssetNum, so i will have a row for every time an Asset has been visited. Many assets may be visited in one day, but there are days where no assets have been visited

I would like to have a moving average over 30 days, preferably centred on the current day.

I know i could do this with RangeAvg(Above(count(AssetNum),-15,30)) if the data had consecutive dates available.

I have tried this formula to calculate the average over the past 30 days, but the distinct counting of the dates in the range isn't working, can someone offer some advice?

Status_Date is the date, and ASSETNUM is a alphanumeric id for each asset.

=count({<Status_Date={">=$(=max(Status_Date)-30)<=$(=max(Status_Date))"}>} ASSETNUM)/ count(distinct {<Status_Date={">=$(=max(Status_Date)-30)<=$(=max(Status_Date))"}>} ASSETNUM)

topic Moving average with non consecutive data in App Development

Moving average with non consecutive data