Max Formula - related question

Sylvia Kay

Hi

I have a table for our Product Development Items. In it I track 3 levels of items: Initiative, Epic, User Story. In a separate column I assign Quarters, e.g. Q1'23, Q2'23, or H1'24 or 2025. For the Parent level I would love to be able to identify which of those is the farthest out quarter. Is this possible?

Best, Sylvia Kay

Paul Newcome

You would need to have a helper column (can be hidden after setup) that has a formula to pull a number value based on the quarter/year combo.

Is the "H" a typo, or is it supposed to be in place of the quarter, and would "2025" be just that, or would it have a quarter listed as well?

Sylvia Kay

Hi Paul,

thank you for your response.

The "H" was not a typo. The farther out, the planning will be less granular: for 2023 we can plan per Quarter, for 2024 we can plan per 1/2 year, beyond that it is per full year. So essentially, I need to assign value to each value, the farther out, the bigger the value. Next, I need to identify the biggest value and translate it back into a meaningful planning value. Is that accurate? This means that as soon as we move forward in the year and planning granularity changes, I need to modify that calculation, correct?

Thank you so much, Sylvia Kay

Paul Newcome

Try a helper column with something like this in it:

=VALUE(RIGHT(Quarter@row, 2) + "" + MID(Quarter@row, 2, 1))

This should output a three digit number that has the two digit year first and then the quarter (or half). In the case of full year it would output the last two digits of the year and then the 0 (from the "20").

Q1'23 = 231

Q2'23 = 232

H1'24 = 241

2025 = 250

You can then reference this helper column to grab the highest number which would correlate to the furthest out timeframe.

Edit to finish:

Then in the parent row of the Quarter column you would use:

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN([Helper Column]@row)), CHILDREN([Helper Column]@row), 0))