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

masonebe

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

Genevieve P.

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

https://community.smartsheet.com/discussion/comment/342400#Comment_342400

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.

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.