Trouble with multiple checks using MAX/COLLECT

I have a set of data for monthly KPI's. The user may enter the same KPI with a different dollar value multiple times. Once I identify the last entry for each combo of calendar month, fiscal year and metric, I will use that data on a report to show the user the value that we'll include later down the road on another report. Even if it's December 2024, the user may enter a new value for July 2024.

Here's a sample of the data - 'metric' names changed for security reasons:

I have the following calculation in the 'Last Entry X' column but as you can see, I'm getting an error:

=MAX(COLLECT([Created On]:[Created On], [Month Number]:[Month Number], [Month Number], [Fiscal Year]:[Fiscal Year], [Fiscal Year], Metric:Metric, Metric@row))

I'm not sure what I'm doing wrong. I want this column to return the maximum created on date for the combo of calendar month, fiscal year and metric. The 'Last Entry X' column is defined as a date column. Any ideas?

Find more posts tagged with

Formulas

Accepted answers

SoS | Dan Palenchar

Hello @hsheridan,

The #UNPARSEABLE error is coming from some references to cells without a row # specified. You must always reference a column, range, or individual cell using @row or a # in your formulas. I've added @row in bold to your formula where it is missing. I assume you are wanting to reference these columns in the same row as your formula.

=MAX(COLLECT([Created On]:[Created On], [Month Number]:[Month Number], [Month Number]@row, [Fiscal Year]:[Fiscal Year], [Fiscal Year]@row, Metric:Metric, Metric@row))

hsheridan

Aha! That did the trick. Thank you so much Dan!

All comments

Philip Robbins

If the syntax of your formula is as shown it's the [Month Number] and [Fiscal Year] that are causing problems. On their own, they should either be appended by @row (if referencing a column) or a # (if referencing a Sheet Summary field).

SoS | Dan Palenchar

Hello @hsheridan,

=MAX(COLLECT([Created On]:[Created On], [Month Number]:[Month Number], [Month Number]@row, [Fiscal Year]:[Fiscal Year], [Fiscal Year]@row, Metric:Metric, Metric@row))

hsheridan

Aha! That did the trick. Thank you so much Dan!

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.