9 Replies Latest reply on Jan 2, 2014 10:44 AM by Matt Lutton

# Attempting to use only MAX([Date]) rows in calculation

Hi All,

I have a table of state transition data that logs the Project ID, Current Status, State, and Date Entered State. I'm trying to find the number of days a given project has been sitting in its current state since the most recent transition to that state.

For example, Project1 may have entered the state of "Ready to Submit" five different times. I only want to count the number of days since its most recent [Date Entered State]. I was thinking that the MAX() function would be perfect for this but so far haven't been able to get it to work the way I want.

My first attempt was the following:

IF [State] = [Current Status]

THEN DATEDIFF('day',MAX([Date Entered State]),TODAY())

END

The problem here is that MAX([Date Entered State]) returns an aggregate and so I can't use it.

My second attempt was slightly more sophisticated. I attempted to use the secondary characteristic of the MAX() function to get around the aggregate problem.

IF [State] = [Current Status]

AND DATE(MAX(DATETRUNC('week',[Date Entered State]),DATETRUNC('month',[Date Entered State]))) = DATE([Date Entered State])

THEN DATEDIFF('day',[Date Entered State],TODAY())

END

But this returns too few results.

I've attached a sample workbook with some sample data and the calculations I've tried so far. Any help would be appreciated as I imagine I'll need to return to this MAX() issue frequently in my future work.

• ###### 1. Re: Attempting to use only MAX([Date]) rows in calculation

I have no insight into the issue, but you can get around the aggregate problem in your first calc by using:

IF attr([State]) = attr([Current Status])

THEN DATEDIFF('day',MAX([Date Entered State]),TODAY())

END

Not sure if this gets you the results you want, though.

1 of 1 people found this helpful
• ###### 2. Re: Attempting to use only MAX([Date]) rows in calculation

Hmmm, thanks Matthew. That definitely gets rid of the aggregation error but the results aren't what I was expecting. When I applied the calculation to the sample workbook, everything looked fine. But when I applied it to my real data, almost every result was null.

In response, I'm uploading a new workbook with much more realistic data (it will replace the old workbook in the first post). Any ideas as to why the calculated field "First Attempt" is returning nulls? ("First Attempt" is using the formula Matthew posted above).

• ###### 3. Re: Attempting to use only MAX([Date]) rows in calculation

Do you know what values you are expecting?  I tried setting up another approach, using LAST() to only include the maximum "Date Entered State" for each project ID:

I did confirm the values appear to be correct for the dates showing. The 8.1 workbook is attached

• ###### 4. Re: Attempting to use only MAX([Date]) rows in calculation

I think that's moving in the right direction. Basically, every Project ID in the sample workbook should end up with a value. If there was a Date Entered State in your second column (referencing the screenshot) then there should be value in the rightmost column.

• ###### 5. Re: Attempting to use only MAX([Date]) rows in calculation

The blank rows are rows where the "Current Status" was not equal to the "New State".

Where they do match, the values should be correct:

If you filter out the ones that don't match, you'll end up with a view like above.

• ###### 6. Re: Attempting to use only MAX([Date]) rows in calculation

Ahh! If you replace the text with a caluclated field that does the following:

DATEDIFF('day',[Date Entered State],TODAY())

Then I think that's exactly what I'm looking for. I'll validate it but it looks like the LAST() approach is going to work. Thanks!

• ###### 7. Re: Attempting to use only MAX([Date]) rows in calculation

Yes, but you'll need to use the AVG aggregation in that case, I believe.  A SUM() will not give the results you want:

• ###### 8. Re: Attempting to use only MAX([Date]) rows in calculation

A quick follow-up. In the workbook you posted, the Last() filter is is set to return a range of 1-1. Shouldn't that be 0-0 instead since 0 would be the current row?

• ###### 9. Re: Re: Attempting to use only MAX([Date]) rows in calculation

After double checking the MAX dates, I concluded that the original was in fact incorrect.  See if this is more appropriate--using the range of 0-0 does make sense.  I also changed the sort behavior to Automatic within the Table Calc--I believe the max dates are correct now, but let me know if you see an issue.

1 of 1 people found this helpful