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

calculating min/max date from timestamp

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

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

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

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

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.