3 Replies Latest reply on May 31, 2011 3:01 PM by philmodjunk

    How to summerize day of week?



      How to summerize day of week?


      I have a field that outputs the day of week name.  How do I count of each day of week?  

      For example, I run a query and get some number of results.  Within those results, I'd like to display the count of each Monday, Tuesday, Wednesday, Thursday and Friday in a footer.  If I have 50 results, I'd like to see something such as:

      Monday 10
      Tuesday 15
      Wednesday 5
      Thursday 5
      Friday 15

        • 1. Re: How to summerize day of week?

          How are the records the same day of the week? Do all the Monday records have the same exact date? or do they have different dates, but all from the same day of the week.

          Please define what you mean by "50 results". Results of performing a find or do you mean something else?

          • 2. Re: How to summerize day of week?

            How are the records the same day of the week?

            If there are 50 results and 10 of them occur on Mondays, that is the same day of the week.  They are all Mondays, even if different dates, which answers your next two questions.

            Please define what you mean by "50 results". Results of performing a find or do you mean something else?

            Yes - through a Find.  I query some criteria and get 50 results.  One of the fields is Date.  it doesn't mean I am querying against date, it is just one of the fields.

            • 3. Re: How to summerize day of week?

              Note, this is very simple as a summary report where the days of the week are placed in sub summary parts. Placing it in a footer greatly complicates what you are specifying.

              Likewise, if we wanted this for all records in the table, it would be fairly easy. For just the current found records, we need some way for such a list of totals to "know" which records are currently in your found set.

              The following method may look a bit "cumbersome", but will work effectively even if the file is shared over a network:

              Define a new table, DaysOfWeek, with these fields:

              DayName: Text
              gFoundList: Text, global storage specified.

              I'll call your current table, Main. If you haven't already defined an auto-entered serial number field in this table to uniquely identify each record, add one and use Replace Field Contents to assign a serial number to each existing record. I'll call that field ID in this example. In this table, also define a calculation field, cDayName to return Dayname ( DateField ) as text.

              Define these relationships:

              Main::anyField x DaysOfWeek::anyfield

              DaysOfWeek::DayName = MainByWeek::cDayName AND
              DaysOfWeek::gFoundList = MainByWeek::ID

              MainByWeek is a new table occurrence of Main--your existing table. You create it by clicking Main in Manage | Database | Relationships and then clicking the button with two green plus signs. (Double click the new occurrence to bring up a dialog where you can rename "Main 2" as MainByWeek.)

              Click over on the Fields tab and add this calculation field, cRecCount, to DaysOfWeek:

              Count ( MainByWeek::ID )  //set it to return number as its return type

              Go to the layout for DaysOfWeek and create one record for each day of the week and enter the name of each day of the week in DayName.

              Now, go to the footer for your layout and add a 5 row portal to DaysOfWeek to it. Place the DayName and cRecCount Fields in the portal to display your totals.

              Now you'll need a script to gather the ID's of your current found set in a list to enter into the gFoundList field so that your portal only counts records from the current found set.

              #After Performing the find....
              Freeze Window
              Go To layout ["ID List" (Main)]
              Copy All Records
              Go To Layout [DaysOfWeek]
              Paste [DaysOfweek::gFoundList]
              Go To Layout [OriginalLayout]

              "ID LIst" is a Main base layout where the only field is the ID field so that CopyAllRecords can capture the list of IDs from the current found set by copying it to the clipboard. gFoundList must be physically present on the DaysOfWeek layout or this will fail.

              You can use the On Mode Enter layout to run this script each time you enter browse mode--that way your counts will update automatically after any find you perform whether manually or by script. Please note that omitting a record or using either of the "Show" options in the Records menu will not update your counts in the footer unless you then run the above script to update the list of IDs.