Moving average with non consecutive data

[beaubellamy]

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)

[Anil_Babu_Samineni]

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)