Nested COLLECT question

I need help with creating the formula for the [PrevHours] column (below).

The expected logic for this formula would be: for a given row's ColValue, find the max date from all instances of ColValue, then find that row's Col2Value.

For example:

Project | Date | Hours | PrevHours

XYZ | 5/1/23 | 10 | ""

XYZ | 5/2/23 | 15 | 10

ABC | 5/1 | 100 | ""

XYZ | 6/1/23| 100 | 15

I want [PrevHours] to refer the previous latest date's entry for hours

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Leibel S

@programd2manage

Add a date type column 'Previous Date' with the below column formula

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Project:Project, @cell = Project@row))

The formula for previous hours is:

=IF([Previous Date]@row <> "", INDEX(COLLECT(Hours:Hours, Project:Project, @cell = Project@row, Date:Date, @cell = [Previous Date]@row), 1), "")

All comments

Leibel S

@programd2manage

Add a date type column 'Previous Date' with the below column formula

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Project:Project, @cell = Project@row))

The formula for previous hours is:

=IF([Previous Date]@row <> "", INDEX(COLLECT(Hours:Hours, Project:Project, @cell = Project@row, Date:Date, @cell = [Previous Date]@row), 1), "")

programd2manage

Great thank you so much! This worked.

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.