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
Qrew LegendI 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
