Average last 30 days

Question

Hello, I have a table with a field Date-Consumption (Date) and Quantity-Consumption (Numeric) and I want to obtain the average consumption of the last 30 days, for example if my consumption begins in MAY/01/2024 to today You should obtain the sum of the last 30 days and divide it by the days that have passed, which for this example would be 20

MarkShnierYou · Answer

I suggest that you make a helper table, called Stats and enter one record into it. It will have record ID of 1.

Then build a relationship where one states has many detail records and let the system create a reference field called related stats. Edit the field properties for that field and make it a formula numeric with a value of 1.

Now you can add summary fields to that relationship, for example, to roll up a summary of the total consumption which was during the current month.

Then, if you need to know how many days have passed in the current month, you can make a field on the stats table with this formula.

Today() - FirstDayOfMonth(Today()) + 1

Sign up

Login to the community