2 Replies Latest reply on Apr 5, 2012 2:54 PM by MegenLeigh

    CountIf for a date range

    MegenLeigh

      Title

      CountIf for a date range

      Post

      OK, so I took on the task of learning Filemaker on my own to assist my team in moving off of an incredibly overworked spreadsheet for employee tracking.  Forgive me if I sound a little ignorant of the software.  I am.  We are tracking the number of customers each employee has referred to our Small Business Dept.  I need to find a way to have it count the number of times an employee name come up from the referral list within a set of date ranges.  Fiscal yr, quarter, month, and week.  Looking at some of the other countIf threads I can see how to build that if I'm just looking for instances of a unique value, for instance I could count the number of refferals that had a purchase associated with it, but without typing in a full quarter worth of dates which isn't scalable say next quarter or next year, I am not sure how to structure the formula.  Suggestions?  Is it even possible without a script?  Like lets say I want to count a range between Dec 31 2011 and March 31 2012 which was our Q2.  Any ideas would be helpful.

       

      More Info: If it helps I have currently 2 tables.  1) is our Refferral list which is importing off of an excel spreadsheet weekly 2) is our Employee List which has the count fields on it.

        • 1. Re: CountIf for a date range
          philmodjunk

          If you have one record (row) for each referral with an employee ID and a date, you can set it up this way:

          Define a summary field in the Referral table as the "count of" a field that is never empty such as the date field.

          Define a relationship between the employee table and the referral table like this:

          Employees::EmployeeID = Referrals::EmployeeID

          This will match an employee record to all referral records with a matching EmployeeID. Now we just need to build in some fields that match by a date range.

          Define some global date fields, gDate1, gDate2. Global storate is an option you can specify on the Storage tab in Field Options. Global storage is not strictly necessary here, but it can be a better option if you Host your database over a network so that multiple users can access it.

          Find the above relationship in Manage | Database | relationships and double click the relationship line so that you can select and add more pairs of match fields to your relationship to get:

          Employees::EmployeeID = Referrals::EmployeeID AND
          Employees::gDate1< Referrals::Date AND
          Employees::gDate2> Referrals::Date

          Now, if you place the summary field from Referrals on your Employees layout, it will report the count of all records that match this relationship.

          And if you already have a different relaltionship between Employees and Referrals that you don't want to change, you can create a new occurrence of Referrals to use in this relationship and the reference to the summary field.

          • 2. Re: CountIf for a date range
            MegenLeigh

            Thank You for the suggestion.  I'm still used to spreadsheets and am just starting to wrap my head around relationships.  I'll give it a shot tomorrow!