6 tmdlTools QAQC

The tmdlTools package has been written and vetted by Elise Hinman. However, she is only human and as such, errors and mistakes occur. It is the responsibility of the Watershed Coordinator to check the calculations of the app to ensure the data and conclusions drawn from the data are sound. The QAQC steps outlined below are intended to help the user gain confidence in their understanding of the data. Any errors in programming should be reported ASAP to Elise Hinman for review and revision. Thank you for your review!

6.1 Preparation

First, run the tmdlTools app, upload your data, run the loading/geomean calculations, and then export your workbook. This exported workbook will be the basis for your QA/QC.

6.2 Dates and Inputs

Make sure the dates make sense in both the app window and the dataset. Translating dates between R and Excel can be troublesome. Check that the dates match between your original workbook and the workbook you just downloaded from the tool. Also, double check that your input values are correct.

6.3 Daily Geomean Checks

In your original dataset (“Ecoli_data” tab), find a date with multiple E.coli concentrations. Navigate to the “Daily_Geomean_Data” tab and find that date in the sheet. Manually calculate the geometric mean between the multiple samples, and ensure this concentration matches the concentration reported in the “Daily_Geomean_Data” E.coli_Geomean record. Make sure you are looking at the right site and date combination. Repeat check with new site and date.

6.4 Monthly Geomean and N checks

6.4.1 In the export

The monthly geomean in the “Monthly_Data” tab of the exported workbook is calculated using ALL data across all years. Filter to one site and month in the “Monthly_Data” tab– this should reveal only one record. In your original dataset (“Ecoli_data” tab), filter to one site and data from one month (across multiple years). Manually calculate the geometric mean of those concentrations and make sure it matches the auto-calculated geomean for that month in the “Monthly_Data” sheet. Additionally, count the number of records for a given site within a given month in the “Ecoli_data” tab, and make sure this count matches the value in the Ncount_mo_C (stands for concentration ncount per month) column. Finally, check the percent reduction, Percent_Reduction_C, needed to meet the geometric mean criterion using the following formula:

Percent Reduction = (Calculated Geometric Mean - Geometric Mean Criterion)/Calculated Geometric Mean

Repeat check with new site and month.

6.4.2 In the app

The application calculates the monthly geometric mean, median, and ncount on the fly given the date range selected in the date widget. Select a date range of interest for a given site in the Month tab of the app, then navigate to the “Ecoli_data” tab in your exported workbook. Filter down to the selected site and time period of interest, and then repeat the same check as above for the date range selected. Be sure to check that the ncount, geometric mean, median, and percent reduction manually calculated from the workbook match what the app reports. Repeat check with new site, month, and date range.

6.5 Seasonal Geomean and N checks

These checks apply to the Rec/Non-Rec and Irrigation/Non-Irrigation tab. Navigate to the “Daily_Geomean_Data” tab in the exported workbook. Filter to one site, year, and season. Check that the manually calculated geometric mean, ncount (Ncount_rec/irg_C), median (not calculated in workbook), and percent reduction needed match the aggregated values reported in the workbook and in the app. Repeat check with new site and year.

6.6 Load Duration Curve checks

Navigate to the “LDC_Data” tab. Pick a single date and ensure that for that site and date in the “Ecoli_data” and “Flow_data” tabs, the values were matched up correctly. Next, use the following formula to ensure the loading in the Observed_Loading and TMDL columns of the “LDC_Data” tab were calculated correctly:

Observed_Loading = [Flow x E.coli Geomean x CF (provided in Inputs tab)]/1000000000

TMDL = [Flow x Geometric Mean Standard (provided in Inputs tab) x CF(provided in Inputs tab)]/1000000000

Loadings are reported in the billions–the reason for dividing by a billion.

For a given site, check and make sure the flow percentile is correct by ranking all flow values between 0 and 100%, with 0 meaning no flows are greater (max flow) and 100% meaning all other recorded flows are greater (min flow).

6.6.1 Monthly and Seasonal Loading checks

Repeat checks 3 and 4 with the loading data, using the “LDC_Data” sheet as your base data for checking the “Monthly_Data” and “Rec_Season_Data”/“Irg_Season_Data” tabs. Your columns of interest for these aggregation checks are now: Observed_Loading, TMDL, Ncount_rec/irg/mo_L, and Percent_Reduction_L.