Index Collect criteria with MAX latest date

MPath

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!

MPath

Paul Newcome

Jul 23, 2024

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

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

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

MPath

Paul Newcome

Jul 23, 2024

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

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

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

Paul Newcome

Jul 25, 2024

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.

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)