9 Replies Latest reply on Apr 29, 2009 9:14 AM by JSha

    Calculate average of multiple fields - new user

    JSha

      Title

      Calculate average of multiple fields - new user

      Post

      I am a new user and I am trying to recreate a function I had in Excel.  I have people who have monthly totals (I input) then I need the 6 month average.  Example Joe - 12/08, 1/09, 2/09, 3/09, 4/09, 5/09, 6 month average.  Each person also appears in 2 other tables so I don't want to create a seperate table for each person.  I only keep 6 months worth of monthly totals so it drops the oldest off every month.  Is there any way to do this?

       

      I need the 6 month average to appear as a field in another table.  I wouldn't mind adding a field and deleting another every month but then I would have to move all the numbers or rewrite a script (I think).

       

      Thanks!

        • 1. Re: Calculate average of multiple fields - new user
          TSGal

          JSha:

           

          Thank you for your post.

           

          Yes, there are several ways to do this.  Here is one way...

           

          Assuming you have input the information into a table entitled "MONTHLY TOTALS".  Possible fields you may have include:

          Employee ID (Text or Number)

          Employee (Text)   (optional)

          Period (Text)

          Total for Period (Number)

           

          I've included an Employee ID field, because it is generally a good idea to keep some kind of unique identifier for each employee, just in case you have two employees named "Joe" or "John Smith".  Plus, if a person gets married and changes their surname, you will not need to modify old records.

           

          Now, in your "EMPLOYEE" table, you may have the following fields:

          Employee ID (Text or Number)

          Last Name (Text)

          First Name (Text)

          Address (Text)

          etc.

           

          Pull down the File menu and select "Manage -> Database...".  Click on the Relationships tab, and you will see a graphical representation of your tables.  Click on the Employee ID field in the EMPLOYEE table, and drag it over to the Employee ID field in the MONTHLY TOTALS table and let go.  Both of these fields float to the top of the tables with a line connecting them together.  You now have a relationship set up.  Click OK and return to the file.

           

          Go to the layout with the EMPLOYEE data, and pull down the View menu and select "Layout Mode".

           

          At the top of the window in the Status Bar, you will see a number of icons.  Placing your cursor over one of these icons will display a tooltip after a second or two.  Towards the middle-right, you will find the Portal tool.  Click on this.  Move your cursor onto the Layout, and it will change into a crosshair.  This will allow you to draw a box, which will become a portal into the MONTHLY TOTALS table.  Draw a box large enough to see a lot of information.  When you let go, you are prompted to "Show related records from" followed by a pop-up.  Select the "MONTHLY TOTALS" table, and click OK.  You will then be prompted for the field names.  Choose the "Period" and "Total for Period" fields, and click OK.

           

          Pull down the View menu and select "Browse Mode".  You will now see ALL monthly totals displayed in the portal.

           

          Now, the trick is to just show just the last six months.  Also, when the next month arrives, we want to remove the oldest totals and replace with the newest totals.  Therefore, this needs to be dynamically updated based upon today's date.  Luckily, FileMaker has a function: Get (CurrentDate).

           

          Now, since you are entering M/YY, we need to put this into a consistent form so that it is easy to sort.  The best way for this instance is YYYYMM format.

           

          Pull down the File menu and select "Manage -> Database..."

           

          Click the Tables tab and select "MONTHLY TOTALS" and then click the Fields tab.

          (Or, we could just select the Fields tab, and in the pop-up on the left side, select "MONTHLY TOTALS" )

           

          Create a new calculation field, "MonthYear" with the calculation formula:

           

          Let (

             [ a = Position ( Period ; "/" ; 1; 1 ) ;

             b = Right ( Period ; Length (Period) - a ) ;

             c = Left (Period ; a - 1) ] ;

             "20" & b & Right ( "0" & c ; 2 )

          )

           

           

          We find the position where the slash occurs and assign it to the variable "a".  We then assign the variable "b" to everything after the slash, and variable "c" to everything preceding the slash.  We then add "20 to the two digit year, and then make sure the month is two digits in length by adding "0" to the front and taking the right two digits.  Therefore, for 4/09, the value would be 200904.  At the bottom, set the Calculation result to "Text".  Click OK, and return to the Fields tab.

           

          In the upper left corner, switch to the table "EMPLOYEE".

           

          First, let's grab today's date and put it into a year and month format like we did in MONTHLY TOTALS.  Therefore, create "g_YearMonth" (I'll explain my naming convention later), a calculation field with the following formula:

           

          Let ( a = Get (CurrentDate) ;

             Year ( a ) & Right ( "0" & Month ( a ) ; 2 )

          )

           

           

          Explanation: This assigns today's date to the variable "a".  We then take the Year of today's date and concatenate it to the right two digits of the Month, like we did in the previous calculation.  If Today's date is 4/22/2009, the formula will return: 200904.  At the bottom, set the calculation result to "Text".

           

          Now, we want this value to appear for every record, so while in the Calculation formula, click on Storage Options... near the bottom right of the dialog box.  Check the box to "Use global storage (one value for all records) ".  By naming it g_YearMonth reminds me that this field is set for Global storage.

           

          Now, we need to also go back six months, so we need another calculation field (also of global storage) with the date six months previously in the same form of YYYYMM.  Therefore, create a new calculation field, "g_YearMonth6", and the formula should be:

           

          Let (

             [ a = Get (CurrentDate) ;

             b = Month ( a ) ] ;

             ( Year ( a ) - If ( b < 7 ; 1; 0 ) ) & Right ( "0" & ( Mod ( b + 5 ; 12 ) + 1 ) ; 2 )

          )

           

           

          Explanation: This puts today's date into the variable "a".  Put the Month into variable "b".  We take the Year, and if the month is less than 7, that means six months previously to that would be in the prior year, so we need to subtract 1.  Concatenate to that a two digit year.  The formula looks odd, but is necessary.  For example, if we subtract six months from June, we would get zero, where we need 12.  Therefore, we add five months to the total, divide by 12 and take the remainder and then add one more.  For June, (6) it returns 11, and add one more for 12.  For July, add 5 (12), divide by 12 and take the remainder (0) and add 1 (January).  Trust me... it works.  :-)

           

          Again, make sure the calculation result is set to Text, and you check the option for global storage.

           

          ------

           

          The final step here is to set the relationship to only display the values over the last six months.

           

          Click on the Relationships tab.  The MONTHLY TOTALS table and EMPLOYEE table are connected via a line, and on that line, there is an icon.  Double-click the icon and an "Edit Relationship" dialog box appears.

           

          On the EMPLOYEE table side, there are fields.  Find the g_YearMonth field and highlight it.  On the MONTHLY TOTALS side, highlight the YearMonth field.  Now we want to find all records where the g_YearMonth field is greater than the YearMonth field.  Therefore, if the EMPLOYEE table is on the left side of the dialog box, then change the equal sign in the middle to >.  If the EMPLOYEE table is on the right side, change it to <.

           

          Click the "Add" button.  In the next box down, you should now see something like:

           

          Employee ID = Employee ID

          AND g_YearMonth > YearMonth

           

          Next, highlight the g_YearMonth6 field and change the operator to ≤ (EMPLOYEE left side) or ≥ (EMPLOYEE right side).  That is, opposite to what you had previously and include the equal portion.  Click the "Add" button again, and you will now see in the lower box:

           

          Employee ID = Employee ID

          AND g_YearMonth > YearMonth

          AND g_YearMonth6 ≤ YearMonth

           

          Click OK a couple of times and return to the file.  Go into Browse Mode, and you will only see the records for the last six months; not including the current month.  If you want to include the current month, then change the relationship above to the following:

           

          Employee ID = Employee ID

          AND g_YearMonth ≥ YearMonth

          AND g_YearMonth6 < YearMonth

           

          That is, switch where the equal sign appears.

           

          ------

           

          The last step is to display an average.  To do this, return to "Manage -> Database...", click on the Fields tab, select the EMPLOYEE table, and create a new calculation field, "Average" with the following formula:

           

          Average ( MONTHLY TOTALS :: Total for Period )

           

          This should result in Number.  This averages the amounts found in the portal.  And since the portal is only displaying for six months, that is what will be averaged.

           

          I know there is a lot of information here, but this should get you pointed in the right direction.  If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Calculate average of multiple fields - new user
            JSha
              

            Looks good, it will take me a few days to put it into action and try it out.  I will post back and let everyone know.

             

            I've written scripts for other programs so I can follow the path and I wrote the Employee file already, so now I'll write the Totals table.

             

            Thanks

            • 3. Re: Calculate average of multiple fields - new user
              JSha
                

              Everything seemed to work great except the g_YearMonth - g_YearMonth6 - portal are not working right with the system date.  I setup the tables as described, the portal showed 6 months and the average worked.  I have another field in the Employees table that calculates how old the person is (years and months) at that field uses current date. 

               

              To test I changed the system date on the computer (this db is local only to my pc until I get everything up, running and approved).  The age field updated but the g_YearMonth - g_YearMonth6 - portal did not.  I changed the storage settings to "do not save" instead of "global" and the g_YearMonth - g_YearMonth6 updates properly but the portal doesn't update.  The portal only updates when I switch settings back and forth on the storage. 

               

              Any thoughts on why the portal is misbehaving?

              • 4. Re: Calculate average of multiple fields - new user
                TSGal

                JSha:

                 

                My apologies.  I forgot that global fields will not update unless forced to.  Therefore, we'll need to update the calculations each time the file is opened.  We'll need to make a few changes, but they are relatively minor.

                 

                First, in the EMPLOYEE table, create a new date field "g_CurrentDate", and set the storage to Global.

                 

                This will be explained later.

                 

                Next, modify the "g_YearMonth" global calculation field to:

                  

                Let ( a = g_CurrentDate ;

                   Year ( a ) & Right ( "0" & Month ( a ) ; 2 )

                )

                 

                That is, we are only removing the reference to "Get (CurrentDate)" and replacing it with "g_CurrentDate".

                 

                We'll do the same for the "g_YearMonth6" field.  That is, change it to:

                 

                Let (

                   [ a = g_CurrentDate ;

                   b = Month ( a ) ] ;

                   ( Year ( a ) - If ( b < 7 ; 1; 0 ) ) & Right ( "0" & ( Mod ( b + 5 ; 12 ) + 1 ) ; 2 )

                )

                 

                ------------

                 

                Let's create a script that will put today's date into the newly created "g_CurrentDate" field.  To create a script, pull down the Scripts menu and select "Manage Scripts..."

                 

                At the bottom left corner of the "Manage Scripts" dialog box is an icon that has a plus sign and says "New".  Click on this to create a new script.

                 

                Name the script "On Open".

                 

                On the left side are all the script steps.  If you scroll down, the first step after the "Fields" subsection is "Set Field".  Double-click this script step, and it should now appear on the right side.

                 

                Click "Specify target field", and a field listing appears.  Select the newly created "g_CurrentDate" and click OK.

                 

                To the right of Calculated result:, click Specify...

                 

                Enter:    Get (CurrentDate)

                 

                ...and click OK.

                 

                Close the script and save it, and then close the "Manage Scripts" dialog box.

                 

                ----------

                 

                One of the features of FileMaker Pro is to execute a script whenever a database is opened.  We'll use the "On Open" script to force an entry into the "g_CurrentDate" field which triggers the other global fields.

                 

                Pull down the File menu and select "File Options..."

                 

                Click the "Open/Close" tab (if not already selected), and under "When opening this file", put a check mark in the box for "Perform script:"

                 

                When the "Specify Script" dialog box appears, select "On Open", and click OK.  Click OK again to return to the file.

                 

                Now, close the file and reopen it.  You should now see the correct values in g_YearMonth and g_YearMonth6, and the appropriate values in the portal.

                 

                Close the file, change the system date, and reopen the file.  There will be new values in the global fields.

                 

                That should do it.  If you need clarification for any of the steps, please let me know.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Calculate average of multiple fields - new user
                  JSha
                    

                  Ok, now the totals work - which is great.  My only problem now is before I set up all these relationships I had the name in the name field in the Monthly Totals table auto-fill on lookup based on Employee ID.  I would enter the Employee ID, the name would automatically fill in (to provide me a double check that I entered the right ID) then I would enter the mm/yy and total.  Now the name field remains blank. 

                  Thoughts?

                   

                  See I setup Monthly Totals, related it to Employee by Employee ID, entered data for averaging.  At this point the auto-fill worked.  I entered in the calculation fields and entered more data, the auto-fill doesn't work.

                  • 6. Re: Calculate average of multiple fields - new user
                    TSGal

                    JSha:

                     

                    I'm glad you got the totals working.

                     

                    Are you looking up information, or just displaying information from the related table?

                     

                    If the former, make sure your Name field in the Monthly Totals table is set up properly for lookup.  That is, pull down the File menu and select "Manage -> Database...".  Click on the Fields tab and select the Monthly Totals table.  Double-click on the Name field, and under the "Auto-Enter" tab, and make sure "Looked up value" is checked and you are looking up from the appropriate table.

                     

                    If the latter, make sure you have the Name field from the related table on the layout; not the current table.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Calculate average of multiple fields - new user
                      JSha
                        

                       


                      TSGal wrote:

                      JSha:

                       

                      I'm glad you got the totals working.

                       

                      Are you looking up information, or just displaying information from the related table?

                       

                      If the former, make sure your Name field in the Monthly Totals table is set up properly for lookup.  That is, pull down the File menu and select "Manage -> Database...".  Click on the Fields tab and select the Monthly Totals table.  Double-click on the Name field, and under the "Auto-Enter" tab, and make sure "Looked up value" is checked and you are looking up from the appropriate table.

                       

                      If the latter, make sure you have the Name field from the related table on the layout; not the current table.

                       

                      TSGal

                      FileMaker, Inc. 


                      I am looking up values as described.  When I created the Monthly Totals table I created a relationship using the Employee ID.  I setup the Name field in Monthly Totals as you desribed looking up from the Employee table.  I entered in some test data and the name auto-filled correctly.  I added the relationships as described before to create the averaging function and went back to add more data.  The Name field no longer auto-filled.

                      I created a new table with just the Employee ID and Name, related it to the Monthly Totals table and changed the lookup from Employees to this new table.  This work-around functions but now I have to put in the Employee ID and Name twice, defeating my check.


                      • 8. Re: Calculate average of multiple fields - new user
                        TSGal

                        JSha:

                         

                        The lookup was working, but now it isn't.

                         

                        First, I would pull down the File menu and select "Manage -> Database...".  Then, click on the Relationships tab, and make sure the relationship between Employee and Monthly Totals is based only on the Employee ID field.  If there are two or more fields involved, then those fields also need to be input before the lookup takes place.

                         

                        In addition, make sure the Employee ID field in the newly created table is linked to the Employee ID field in Monthly Totals.

                         

                        Click on the Fields tab, select the newly created table, click on the Name field, click on Options..., and enter a lookup into Monthly Totals and copy the Name field.

                         

                        Now, when you go to Browse Mode in the new table, enter an Employee ID, FileMaker will attempt to lookup the Name from the Monthly Totals with the same Employee ID.

                         

                        If this still isn't working, make sure the Employee ID in the new table is the same field type as the Employee ID in Monthly Totals.  If one is Text and the other Number, the lookup may fail.

                         

                        TSGal

                        FileMaker, Inc.

                        • 9. Re: Calculate average of multiple fields - new user
                          JSha
                            

                          TSGal wrote:

                          JSha:

                           

                          The lookup was working, but now it isn't.

                           

                          First, I would pull down the File menu and select "Manage -> Database...".  Then, click on the Relationships tab, and make sure the relationship between Employee and Monthly Totals is based only on the Employee ID field.  If there are two or more fields involved, then those fields also need to be input before the lookup takes place.

                           

                          In addition, make sure the Employee ID field in the newly created table is linked to the Employee ID field in Monthly Totals.

                           

                          Click on the Fields tab, select the newly created table, click on the Name field, click on Options..., and enter a lookup into Monthly Totals and copy the Name field.

                           

                          Now, when you go to Browse Mode in the new table, enter an Employee ID, FileMaker will attempt to lookup the Name from the Monthly Totals with the same Employee ID.

                           

                          If this still isn't working, make sure the Employee ID in the new table is the same field type as the Employee ID in Monthly Totals.  If one is Text and the other Number, the lookup may fail.

                           

                          TSGal

                          FileMaker, Inc.


                          There in lies the problem, which is what I thought.  The Employee file and Monthly Totals file do not and cannot have just one relationship because then the average wouldn't work.  As above:

                           

                          "Click on the Relationships tab.  The MONTHLY TOTALS table and EMPLOYEE table are connected via a line, and on that line, there is an icon.  Double-click the icon and an "Edit Relationship" dialog box appears.

                           

                          On the EMPLOYEE table side, there are fields.  Find the g_YearMonth field and highlight it.  On the MONTHLY TOTALS side, highlight the YearMonth field.  Now we want to find all records where the g_YearMonth field is greater than the YearMonth field.  Therefore, if the EMPLOYEE table is on the left side of the dialog box, then change the equal sign in the middle to >.  If the EMPLOYEE table is on the right side, change it to <.

                           

                          Click the "Add" button.  In the next box down, you should now see something like:

                           

                          Employee ID = Employee ID

                          AND g_YearMonth > YearMonth

                           

                          Next, highlight the g_YearMonth6 field and change the operator to ≤ (EMPLOYEE left side) or ≥ (EMPLOYEE right side).  That is, opposite to what you had previously and include the equal portion.  Click the "Add" button again, and you will now see in the lower box:

                           

                          Employee ID = Employee ID

                          AND g_YearMonth > YearMonth

                          AND g_YearMonth6 ≤ YearMonth"

                           

                          That is the relationship.  So I have to use the additional table I setup so it has a source of information that has a singular relationship. 

                           

                          The workaround works, and the auto-fill is less important than the averages.  So unless there something else I should do instead I'm good with the solution at hand.

                           

                          Thank you for all your help.