Moving average with non consecutive data

beaubellamy — Sat, 16 Nov 2024 04:59:19 GMT

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)

Re: Moving average with non consecutive data

Anil_Babu_Samineni — Sat, 17 Aug 2019 14:04:22 GMT

Perhaps this?

=count({<Status_Date={">=$(=Date(max(Status_Date)-30), 'Original Format'))<=$(=Date(max(Status_Date), 'Original Format'))"}>} ASSETNUM)/ count(distinct {<Status_Date={">=$(=Date(max(Status_Date)-30), 'Original Format'))<=$(=Date(max(Status_Date), 'Original Format'))"}>} ASSETNUM)

topic Re: Moving average with non consecutive data in App Development

Moving average with non consecutive data

Re: Moving average with non consecutive data