3 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    Summary Maximum / Minimum Dates

    TH _

      Is there a way to create a summary from this tabled data sample that pulls only the minimum date and maximum date for the employee by day:

      Employee     Date Completed

      Smith, Walter     09-14 08:25:27

      Smith, Walter     09-14 08:26:53

      Smith, Walter     09-14 08:31:16

      Smith, Walter     09-14 08:44:05

      Smith, Walter     09-14 08:46:24

      Smith, Walter     09-14 08:49:56

      The summary data would read:

      Employee       Min Date          Max Date

      Smith, Walter  09-14 08:25:27    09-14 08:49:56

      I am a 15 year Monarch user but recently upgraded from V3 to V8, thank goodness.

        • Summary Maximum / Minimum Dates
          Grant Perkins

          Hi TH and welcome!

           

          Quite a challenge for a first post!

           

          I can't think of a direct way to do this without any date handling functionality in the summaries. You have an added problem in that the date part of the your data is really only MM/DD so not a valid date format as it stands if you want to do any calculations with it.

           

          If you have a way to get a year number as well it would help with the change of year period if that is important. It would certainly make things easier.

           

          Other than that I can see a way to get a 2 line summary for each employee which you might then export to a new report and re-process into a one line summary. Would that help?

           

          Here is the idea.

           

          Use a calcualted field to construct a full mm/dd/yy date component and concatenate the time to it, defining it as a date/time field with short date and time properties.

           

          Add another calculated field to make a direct unchanged copy of the new field.

           

          Create a summary with the Employee field and the 2 new calculated fields as the keys.

           

          Set one of the date fields to sort ascending and the other to descending. For each field use the summary field properties in the MATCHING tab to display the 'FIRST "1" VALUE'. The accumulated values will show as 'All Others' but you can blank that description if you like.

           

          You should now see a summary that looks like this

          [font="courier"]Name               Date                  Copy of Date

          Smith, Walter     2006-09-14 08:25:27     

          Smith, Walter                       2006-10-01 08:09:56

          Smith, Walter     /font[/quote]approximately depending upon how well it posts here!

           

          So Date = Early date and Copy of date = Latest date. The 3rd row is a byproduct of the records in between and can be ignored.

           

          To get it onto one line, using Monarch, I would export it to a fixed width report file and then run a simple model to pick the 3 fields and present them on one line but I am sure there are ther ways to approach that.

           

          If you can't get the year number you could still work out a way to construct some sort of field(s) that could be sorted as effectively for the summaries - but is is much easier I reckon if you can get the year from somewhere and concatnate it into the middle of the MM/DD and time strings as part of creating a date field.

           

          HTH.

           

           

          Grant

          • Summary Maximum / Minimum Dates
            TH _

            Grant, thanks for the response, first of all.  We are on the same wavelength.  If, If, If a sorting capability within a table was possible the answer would be easy.  In other words, let's say we have 3 columns labeled employee, date, time, and another 3 columns with the same information.  Within an Excel spreadsheet you can sort the last three columns in descending order based on time.  In other words, for the same date, on the first line would be employee, ascending date, ascending time, employee, ascending date, and descending time.  This setup would also allow me to subtract ascending time from descending time to get a duration for the same employee, day.  ...And maybe (I say maybe because I haven't test this part yet) allow the summary to maybe pull the maximum result (duration) by employee, day.  In the meantime, I'll just detour the initial numbers and sort the last three columns in Excel for the secondary setup in Monarch.

            Thanks for your help.

            Tony

            • Summary Maximum / Minimum Dates
              Grant Perkins

              Hi TH,

               

              I slightly modified you sample lines to add in the year number (to make it easier to prove the theory quickly rather than have to create a calculated field formula) and test the month roll over vs time just to be sure.

               

              I sorted the 2 date fields differently in the summary in Monarch, both with the First Value only displayed, to get the 3 line result 0 effectively all the combinations of data possible with the third line really showing "All Others" in both columns.

               

              If it were my problem I would now export the result to a new report format. The create a simple model based on a 2 line sample (I think 2 will do it but it would depend on your data) and select the 2 date fields and the Employee name.

               

              That would give me a one line record of 3 fields.

               

              Add a calculated field to to the 'time' difference calculation between the two date fields using the Version 8 'AGE' function and you should be somewhere close to the output you need.

               

              Export wherever you wish when happy!

               

              When completely happy automate the entire process with a batch file or VB code.

               

               

              Have I missed something?

               

              Grant