How do I get a report that shows the average value for a variable since a certain condition occured?

For example, I want to see the average Effluent BOD since the last time it went above 40. 

1. Find the Varid for Effluent BOD.  In this example it is 37. 

2. Get into Spread Report Design.

3. Click on cell D4, and go to Locate, SQL Results.

4. Enter the following query (replacing the VARID = 37 with your Varid) and click OK.  Assumes your variable is daily and therefore data is in the Datatbl, see WIMS Data Structures for more information.

Select TOP 1 DateStamp FROM DATATBL WHERE CURVALUE > 40 AND VARID =37 ORDER BY DateStamp DESC

5. Locate the Current Date in cell D5 using Locate, Dates

6. In D7, enter the formula =GAVG(4011,D4,D5).  This will return the average for variable 4011 (effluent BOD, Varid 37 in this case) for date range displayed in D4 and D5.

Download Example Template: TipAvgSince.hwr (Click here for download instructions)