Forum Discussion

Nelio's avatar
Nelio
Qrew Member
7 months ago

Average last 30 days

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

  • 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