Advanced Filtering by MAX value

Hi all

I've created a report and wanted to know if there is a way to show unique rows by the highest date value

For example - below I would like to see the bottom table as my report (colours are just to show the matching rows - they are not part of the conditions required) where only the unique rows are pulled through (unique value in Date of Check column)

or would there be a way I could create a helper column in the source grid which looks at multiple columns and identifies which one has the latest date of all the rows that match otherwise?

Thanks

Dan

Find more posts tagged with

Formulas

Reports

Accepted answers

Mark Cronk

Hi Daniel,

Helper columns are the solution. Add a text/number column [Join] with the formula:

=JOIN(name@row:location@row, ", ")

Add a checkbox column [report] with the formula:

=IF([all checks complete]@row="N", 0, IF(countifs(join:join, join@row)=1, 1, IF(countifs(join:join, join@row, [date of check]:[date of check], @cell>[date of check]@row)>0, 0, 1))

Filter your report to include [report]=1.

Work for you?

Mark

All comments

Mark Cronk

Hi Daniel,

Helper columns are the solution. Add a text/number column [Join] with the formula:

=JOIN(name@row:location@row, ", ")

Add a checkbox column [report] with the formula:

=IF([all checks complete]@row="N", 0, IF(countifs(join:join, join@row)=1, 1, IF(countifs(join:join, join@row, [date of check]:[date of check], @cell>[date of check]@row)>0, 0, 1))

Filter your report to include [report]=1.

Work for you?

Mark

Paul Newcome

Didn't we already figure this out the other day HERE? Or were you looking for something different?

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.