Profile Picture

Pull "Max" value from array of DateTime values

Posted By Bob Smith 11 Years Ago
Author
Message
Bob Smith
Posted 11 Years Ago
View Quick Profile
Forum Guru

Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)

Group: Forum Members
Last Active: 11 Years Ago
Posts: 69, Visits: 1

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.



Attachments
DateTimeArray.gif (414 views, 5.00 KB)
Nevron Support
Posted 11 Years Ago
View Quick Profile
Supreme Being

Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)Supreme Being (4,329 reputation)

Group: Forum Members
Last Active: Last Year
Posts: 3,039, Visits: 3,746

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.



Best Regards,
Nevron Support Team



Bob Smith
Posted 11 Years Ago
View Quick Profile
Forum Guru

Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)

Group: Forum Members
Last Active: 11 Years Ago
Posts: 69, Visits: 1
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!

Bob Smith
Posted 11 Years Ago
View Quick Profile
Forum Guru

Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)Forum Guru (69 reputation)

Group: Forum Members
Last Active: 11 Years Ago
Posts: 69, Visits: 1
Worked great! Thank you



Similar Topics


Reading This Topic