Index Collect criteria with MAX latest date

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!

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

MPath

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)

All comments

Paul Newcome

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

MPath

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

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

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.