How to show most recent entries in Dashboard widget based on date entered?

The ultimate end result I want is to show in a dashboard the 5 most recent items that have been completed.

The Primary Column on the sheet lists the names of properties, and each subsequent column shows a task that has been completed as dropdowns that can be either left blank or marked "Complete". Two of the columns are Date formats, so that the dates of when the first and second set of steps are completed can be added. I want to show when a date is added to Step 1 Complete column, the corresponding property name will appear in a list in a dashboard. Ideally I'd like to be able to show the 5 most recent dates/properties.

I've tried using the MAX and COLLECT functions with no luck, I get a "Incorrect Argument Set" with this formula:

=MAX(COLLECT([Property Name]1:[Property Name]141, [Step 1 Complete]1:[Step 1 Complete]141), 1)

What am I missing?

Find more posts tagged with

Accepted answers

All comments

Kleerfyre

Why not just create a report and filter it for the most recent dates. Then you could just use a report widget and it would supply your dashboard with the needed data.

Leibel S

@Loukas W

Whether you want to show this in a metric or in a report you would need to rank your entries by date, then take the top 5.

To rank by date using the RANKQ formula you will need to convert your date into a number using a formula like the below courtesy of @Paul Newcome :

=IF([Completed Date]@row <> "", VALUE(YEAR([Completed Date]@row) + "" + IF(MONTH([Completed Date]@row) < 10, "0") + MONTH([Completed Date]@row) + "" + IF(DAY([Completed Date]@row) < 10, "0") + DAY([Completed Date]@row)), "")

I called this formula DateNum

Then create a 'Rank' column using a formula like the below:

=IFERROR(RANKEQ(DateNum@row, COLLECT(DateNum:DateNum, DateNum:DateNum, @cell <> ""), 0), "")

Then you can either make a report filtered to Rank 5 and under. Or you can create a widget referencing a cell/sheet summary that has a formula like the below:

=JOIN(INDEX(COLLECT([Column]:[Column], Rank:Rank, @cell <= 5), 0), CHAR(10))

Please note the RANKQ formula will return 5 for all entries that are 'tied for' 5.

Loukas W

@Kleerfyre I would prefer to do whatever is simplest, so if there is a simple way to do this, I'm all for it. Can you provide some guidance on how to do it?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.