Pull "Max" value from array of DateTime values


https://www.nevron.com/Forum/Topic7514.aspx
Print Topic | Close Window

By Bob Smith - 11 Years Ago

I have a list of datetimes that are in an array and seem to be "out of order". Attached is a screenshot that shows them in their relative index values (0 through 6)

I need to extract the earliest and latest datetimes so that I can subtract them from one another and calculate the number of months between them. The LAST() value from the array does not always return the latest datetime value. How can I go about doing this?

The MAX() function works only on integer/double values.

By Nevron Support - 11 Years Ago

Bob,
If you are grouping by Date, on the Category grouping you can first set Sorting. For example:
Expression: =Fields!Date
Direction: Ascending

You can get the first and last dates by using the following expressions:
="First date: " + MINVAR(Fields!Date)
="Last date: " + MAXVAR(Fields!Date)

What do you mean by “calculate the number of months between them”?
Calculating months between two dates cannot be calculated precisely. By using the Time Span Functions, you can get the total days (hours, minutes, seconds or milliseconds) between the two dates:
="Total days: " + TOTALDAYS(TIMESPAN(MAXVAR(Fields!Date) - MINVAR(Fields!Date)))

To get the months between the two dates, you can also use the Round Up and Round Down methods.

Round Up - This method does not use the day of the month in its calculation. For example, given a start date of 10/31/2013 and an end date of 11/2/2013, one month is returned even though only two days elapsed. For this method, use the following expression:
="Round Up method: " + ((YEAR(MAXVAR(Fields!Date))-YEAR(MINVAR(Fields!Date)))*12+MONTH(MAXVAR(Fields!Date))-MONTH(MINVAR(Fields!Date))) + " months"

The number that is returned equals the number of months from MINVAR(Fields!Date) to MAXVAR(Fields!Date) date, rounded up to the next whole number.

Round Down - This method uses the number days in its calculation and rounds down to the nearest number of whole months. For example, given a start date of 10/30/2013 and an end date of 11/30/2013, one month is returned; however, an end date of 11/29/2013 returns a zero. For this method, use the following expression:
="Round Down method: " + (IF(DAY(MAXVAR(Fields!Date))>=DAY(MINVAR(Fields!Date)),0,-1)+(YEAR(MAXVAR(Fields!Date))-YEAR(MINVAR(Fields!Date)))*12+MONTH(MAXVAR(Fields!Date))-MONTH(MINVAR(Fields!Date))) + " months"

The number returned equals the number of months from MINVAR(Fields!Date) to MAXVAR(Fields!Date) date, rounded down to the nearest number of whole months.

By Bob Smith - 11 Years Ago
Thank you for the reply! I will give those functions a try just as soon as I get a moment.

My situation is somewhat unique as I am just using the number of months to scale the size of the chart area. The chart will render with a size of something like 30*number of months. In this case, it doesn't have to be a completely precise value, though seeing a timespan function using the minvar maxvar functions will be very useful in the future!

Thanks again - you guys are always on the ball!
By Bob Smith - 11 Years Ago
Worked great! Thank you