Max Formula - related question

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

SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

PLATFORM ENGINEERING & TECHNOLOGY TEAM

AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

AMEXGLOBALBUSINESSTRAVEL.COM

follow us on twitter: @AmexGBT

follow us on instagram: @AmexGBT

Answers

  • Paul Newcome
    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
    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

    SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

    PLATFORM ENGINEERING & TECHNOLOGY TEAM

    AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

    M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

    AMEXGLOBALBUSINESSTRAVEL.COM

    follow us on twitter: @AmexGBT

    follow us on instagram: @AmexGBT

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/18/23

    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!