Fix for Michigan eDMR Part A using Maximum Monthly or 7-Day Geometric Mean [Q14177]

	Browse		Search		Login

Home : Products : Hach WIMS : Hach WIMS Client : Documentation : Main Menu : Design : Spread Reports : eDMR / eReport : Michigan eDMR : Fix for Michigan eDMR Part A using Maximum Monthly or 7-Day Geometric Mean

Q14177 - HOWTO: Fix for Michigan eDMR Part A using Maximum Monthly or 7-Day Geometric Mean

This article explains how to fix the entry for Fecal Coliform and any other entry that uses the statisitical base code "MAX MONTHLY GEOMETRIC MEAN" or "MAX 7-DAY GEOMETRIC MEAN".

Hach WIMS Client will be updated in the next release (currently at version 7.4.1) to include these two statistical base codes.

If one of the following messages appear when locating Michigan eDMR entries for NPDES Row Values (Part A), then follow the steps below.

MAX MONTHLY GEOMETRIC MEAN

MAX 7-DAY GEOMETRIC MEAN

First of all, let's get in the right place.

Run Hach WIMS Client
From the menu bar, select Design
From that list, select Spread Report
Click on a cell to begin the entry and select:

Locate
eDMR / Electronic Report Link
Michigan eDMR
NPDES Row Values

Now you should see the following display for the Michigan eDMR Part A
The top window may be blank, to fill this in:

Click Load button in top right corner
Navigate to your latest Michigan DMR Part A XML file (something like DMR_PartA_2014_10.xml where 2014 is the year and 10 is the month).
Select that file and click Open button

When we selected Fecal Coliform, we had two entries in the lower right in our example:

AVERAGE requests the statistic Max Monthly Geometric Mean
MAXIMUM requests the statistic Max 7-Day Geometric Mean

Select the variables for the entries, in our example we selected variable 4021 - Effluent Fecal Coliform, you may need to select something different
To show the symbols, click the "Print <,>" box.
Click Ok button

Spread Report Design will go through and create necessary formulas and format the report for you. However, in our case, we get both of the messages and we need to fix that.

The Quick Change:

Change the formulas according to the following:

Statistical Base Code	Formula Inserted	Change To This
Max Monthly Geometric Mean change	GAVGZ(VarNum,1,"R",0,1)	GGMZ(VarNum,1,"R",0,1,1)
	GAVG(VarNum,1,"R",0,1)	GGM(VarNum,1,"R",0,1,1)
Max 7-Day Geometric Mean	GAVGZ(VarNum,1,"R",0,1)	GMAXZ(VarNum,1,"R",0,1)
	GAVG(VarNum,1,"R",0,1)	GMAX(VarNum,1,"R",0,1)

How To Change the Formula:

Once the Michigan Part A entry is created for the parameter, you will need to locate the cell to be changed.

Look for the statistical base code, it will be the second column, first row of each significant area of the parameter (our example parameter is Fecal Coliform).
Move the cursor to the next column and down one cell at a time, while watching the Formula Bar.
Find the cell with the above Formula Inserted in the Formula Bar.
Click the elipse [...] button to the left of the Formula Bar to open the Spread Formula Editor.
Edit the formula to match the text in Change To This column for your variable.
Click OK button.
Repeat these steps for any parameters using either of the two statistics mentioned above.

Don't foget - save the form when you finish making changes.

So in the picture example above, I have "Max 7-Day Geometric Mean" in cell D28, and my formula in E29 is GAVGZ(4201,1,"R",0,1). I click on the elipse button and change it to GMAXZ(4201,1,"R",0,1) and click OK button.