4 Replies Latest reply on Feb 9, 2017 7:43 PM by user19752

    calculating min/max date from timestamp

    rubinjm

      I have a timestamp field and would like to calculate the first and last date in my group of records so I can display those on a chart.

       

      I have tried creating calculations using

      Min ( timestamp ) and Max ( timestamp )

      with global storage and calc result as a date

       

      but maxDate is showing as 2/9/17 and minDate is showing as 2/9/17.

       

      Min should be 2/8 and max should be 2/9.

        • 1. Re: calculating min/max date from timestamp
          philmodjunk

          I just tested this and cannot reproduce the problem.

           

          How are you using these aggregate functions?

           

          Correct syntax would be:

           

          Max ( RelatedTable::TimestampField )

           

          Max ( Repeatingfield )

           

          Max ( Field1; Field2; Field3 ....)

           

          Max (Field ), on the other hand, just returns the value of Field in the current record. Could that be what you did here?

          • 2. Re: calculating min/max date from timestamp
            rubinjm

            Yes--it is set up as Max (field). I was thinking that with global storage it would return the Max within the set of records.

            How should I do it to return the min and max of all of the records in the database?

            • 3. Re: calculating min/max date from timestamp
              philmodjunk

              A summary field would return the max value over all records in your found set. That will be over the entire table if you show all records.

               

              Or

               

              If you set up a Cartesian self join:

              YourTable::anyfield X YourTable 2::anyfield

               

              Then max ( yourTable 2::time stamp field )

               

              would return the max over the entire table

               

              Or

               

              executeSQL( )

               

              could be used

               

              but if this is a timeStamp that is auto-entered when the record is created, you could get this value much faster by other means that accesses the most recently created record.

              • 4. Re: calculating min/max date from timestamp
                user19752

                You can use summary field "max (or min) of timestampField" instead of calculation, for sorted "group of records".

                 

                Or you can define relation for using Max(), "anyfield X anyfield" for all records in table. "group = group" for group of records.