How can I show Today, where someone stands within the graph?

Hi Community,

I am trying to build something that will show %complete, desired %, AND where the trainee stands in comparison to the milestones all in one graph. From the progression status indicators, I have a few items that show some data. From the trainees start date and selected target finish date, I have made an equation to create milestones in between. My hope with this is to give some indicator to help determine if they are on track or not.

Now that I have this made, I am wanting to show where the trainee stands TODAY in this 8 months long process. i.e. if we are in month 2, we are ahead... if we are in month 4+ we are behind.

My first approach to answering this was to create the Status column. The equation is shown below. I feel that I an getting close to my answer but it is not complete. What else can I calculate and visualize to show if someone is behind or ahead of the set start and targeted finish dates?

Thank you


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @masonebe

    The Status column looks great!

    If you want to display just the relevant month's data, what I would do is set up a field in your Sheet Summary area of your Sheet. Then we can use a Formula to find the row that has today's Work Week and grab the most recent Work Week in your sheet based on that number to show the "Current Status":

    =INDEX(COLLECT(Status:Status, WW:WW, MAX(COLLECT(WW:WW, WW:WW, <=WEEKNUMBER(TODAY())))), 1)

    This finds the MAX Week Number, as long as that Week Number is less-than-or-equal-to Today's Week Number, and returns the Status ball from that row into a Summary Field:

    This way you can select the Summary Field in a Metric Widget to display it on your Dashboard without needing to update the widget every week.

    You can of course build the same formula but returning the % Complete and Desired % to then use only those two values in a chart as well:

    =INDEX(COLLECT([% Complete]:[% Complete], WW:WW, MAX(COLLECT(WW:WW, WW:WW, <=WEEKNUMBER(TODAY())))), 1)

    Let me know if this answered your question or if I can help with anything further!

    Cheers,

    Genevieve

  • masonebe
    masonebe ✭✭✭

    Hi Genevieve,

    I have this data presented in a Report and not a sheet. How can I transfer the data to then use the sheet summary function?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @masonebe

    Are the rows coming from different sheets or are they all from the same source sheet? If the content is stored on the same source sheet, you could use the formula in that sheet instead of the Report.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!