Finding the latest date of certain tasks based of those tasks' attributes

Kelsey L

Hello, I'm looking to find the latest end date for certain tasks based off of attributes in those tasks' rows.  For example: If I want to find the latest date of tasks in cycle 1 of a release (but not cycle 2), and within that there are 5 different categories of tasks but I only want to know the latest end date of one of those categories.  I'm hoping that some combination of forumlas for Smartsheet can acheieve this, but I'm unsure how!

John Creason

Kelsey -

 

I'm curious to watch the discussion unfold on your question.  I can see how to get this in a report, but I am pretty sure that's not what you want.  I am not actually sure of a good way to do this.  If you're familiar with SUMIFS, then you could imagine something like MAXIFS getting you the answer.

 

Let's see what the community comes up with, and if there's not a good solution, maybe we add a function to make this possible.

 

Kelsey L

Hi John!  Thanks for your comment.  Is this functionality actually possible in a report?  Just curious.  I need it to happen for about 5 different scenarios, and am not trying to make 5 different reports, but let me know. 

John Creason

Got it...  Five reports is not what you want.  Sounds like we need to think about adding some capability in the formula engine for this sort of scenario...  Let me look into this and get back to you.

J. Craig Williams

This sounds possible with the current formulas, but I'd need to see the data to be sure.

 

Craig

John Creason

Craig - I'd be impressed if you could pull it off!  It sounds like Kelsey is asking for a MAXIFS function which doesn't exist.  You might be able to do some serious gymnastics to get there...  But what if we gave you a function such as this:

 

FILTER(range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

 

Where FILTER returned a collection which was that subset of range which met all of the criteria.  You could then do this:

 

MAX(FILTER(range, criteria_range1, criteria1, [criteria_range2, criteria2], ...))

 

And that's what Kelsey is looking for.  What's more is that I am sure that once it's out there, others will find ways to make use of it.  I've been checking the community lately because we're going make improvements to properly handle TIME in functions.  I wanted to see what other trends were being raised.

J. Craig Williams

John,

 

I'd need to see the data, but you are probably right.

In this case, would a return of a row number reference also be part of a solution?

 

The way I have worked around not having a MAXIFS function has been to determine the MAX in some collection elsewhere and then perform comparisons against that, not against the group.

It hasn't worked in every case, but in a major portion. 

The drawback is (and this applies to yours too) is if you have several values that are equal to the max - in Kelsey's example have two tasks in a group that have the same end date.

 

Craig

 

Andrew Stewart

Bumping this thread as I would also like a MAXIFS (or MAX(FILTER(...)) function.

My particular scenario is trying to create summary lines in a report where there are tasks assigned to people, each task having an end date and a duration left.

I would like to calculate for each person the total effort left and the latest end date, so that I can raise a warning if the total effort left is greater than the working days to the latest end date. The total effort is easy with SUMIFS, but I need a MAXIFS for the latest end date of tasks assigned to the person.

It is a simplified version of resource management. I don't believe I can use the Smartsheet feature in this situation because it appears (to me, I have not looked very thoroughly, so I could be wrong) that it requires users to be allocated to tasks as a percentage of time. Our tasks are such that although resources can swap between tasks before they are complete because there are slack periods waiting for others to complete parts of it, they are unlikely to work on more than one in a given day. We work in a very agile fashion, I just need to know if they are being asked to achieve the impossible.

Any ideas?

Andrew Stewart

Hi John,

It appears someone at SmartSheet did do just as you offered, but decided to give it a different name. 

 

I just stumbled across the the COLLECT function, which does exactly what I need.

Andrew Stewart

SOLVED - MAX(COLLECT(range, criteria_range1, criteria1,...)

I did not RTFM. In my defence, though, COLLECT is not an intuitive name, I am used to terms like Filter, Subset, Extract, etc....