How do I structure my data to show a fixed and autonomous double line graph?

masonebe

Hi Smartsheet Community,

I am looking to create a double line graph to help show if someone is on track or falling behind. This is my idea:

The red line is fixed and is decided based upon the start and targeted finish date. The black line is data that is pulled each week via workflow and put onto a report. I am wanting to use milestones (blue circles) to help dictate if they are falling behind or not. i.e. The student should be at 25% once they get to 1/4 from their start date, etc.

I currently have a couple of workflows that I have made to pull the % complete each week. This data is then being used via Report to be used in the future for the graph. This is not the issue.

The issue I am running into is how to format the fixed dates and align them with the Report data to create this graph.

I currently have this made to calculate each milestone once the start and targeted finish are set. Along with the dates, I have a WW that I have Vlookup'ed to correlate with the Workflow Report Data. Milestone days is: Finish date - Start Date.

Should I be setting up my data in a different way?

I initially wanted to pull these columns into the same report as the above Workflow report, however I am running into issues when graphing.

How do I format the data via report or grid to show the above figure?

Thank you!

masonebe

View below

Paul Newcome

You would need to flip the structure so that you are working down two columns that are side by side. One for the projected and one for the actual.

Paul McGuinness

Hello @masonebe

To deliver that graph you will need to structure your data like this

will deliver this result when added to a dashboard widget

Hopefully this helps you decide how to proceed next.

Thanks

Paul

masonebe

View below

masonebe

Is it possible to have two Date columns in a report to create this graph? I have my target % and dates, along with a workflow that populates a % complete and date each week. Is that viable?

Paul Newcome

You would make it so that you have a single date column. Then you would input the projected and actual % in their own columns for each of those dates.

masonebe

I am trying to automate this graph. I am not trying to input the data each week. This is why my reference sheet for the graph is a report, it allows me to automatically have data be entered each week to track and find a trend. When I input the dates, there is an error when the automation workflow populates its % complete and date/ Work week into the report.

The first row is from an automation pull, while the rows below are associated with the milestone dates and target %. When the workflow triggers, the WW and the %complete will populate underneath the 5 rows that were just added. Is there a way to work around this? or does smartsheet not have the capability I am looking for?

masonebe

A 12 Pm Workflow just added data to the report. It is segmenting the two, not creating integrated data, is there a way to connect them?