Index Collect criteria with MAX latest date

MPath · 07/23/24

Hi,

I've been hitting myself over the head for not 'getting' it, I think i'm doing the correct thing, but obviously since I'm reaching out, I am NOT ;)

If you would be so kind, please enlighten me on my error.

In a sheet summary I'm trying to do the following:

=INDEX(COLLECT(ReportSummary:ReportSummary; TypeReport:TypeReport; "Accident"; DateOccurrence:DateOccurrence; MAX(DateOccurrence:DateOccurrence)); 1)

I would like to collect the ReportSummary in the summaryfield.

Wit the following condition:

-Latest entry as per DateOccurence (It IS a Date-column)

-and where TypeReport is Accident.

Thanks in advance!

Paul Newcome · 07/23/24

Are you getting an error message or an unexpected output? Where exactly are you putting this formula?

MPath · 07/25/24

https://community.smartsheet.com/discussion/comment/445979#Comment_445979

Paul Newcome to the rescue, once again!

To answer your question;

In the summary fields

Also tried

=COLLECT(ReportSummary:ReportSummary; TypeReport:TypeReport; "Accident"; DateOccurrence:DateOccurrence; MAX(DateOccurrence:DateOccurrence); 1)

Paul Newcome · 07/25/24

The problem is with using the MAX function by itself. It is pulling the max date across all dates regardless of the type. Try making it a MAX/COLLECT so that you can filter the MAX to bring in only dates for the appropriate type.

MPath · 11/21/24

https://community.smartsheet.com/discussion/comment/446321#Comment_446321

I ended up using a helper sheet because I was lost. I just ended up dr.googlen this topic(my own…) because I have again the same issue with different sheet, hahaha, omg;

In summary of a sheet I am trying to pull latest date of accident.

=MAX(COLLECT(Datum:Datum, [Type melding]:[Type melding], "Ongeval"))

Gives me invalid column value error. Datum is a Date colum(checked)

Type melding translates into Type of report, Ongeval means accident.

=MAX(COLLECT(Datum:Datum; [Type melding]@row; "Ongeval"))

Give me a unparseable error.

I always feel like such a noob…Thought? :)

EDIT: Yeah sooooooo, it's PROBABLY (Most certainly) a good idea to ad a DATE FIELD in summary when doing such a thing…. So fixed

=MAX(COLLECT(Datum:Datum, [Type melding]:[Type melding], "Ongeval"))

Was correct! (the good news)

Index Collect criteria with MAX latest date

Answers

Help Article Resources

Categories