New Graph functionality

Stevie Goodwin

Hi,

 

We have been pining after graph functionality for a while in smartsheet and thought the latest release may have addressed this. 

 

Could anyone suggest a way to address the following (please see attached picture)

 

We would like to add a target line to a bar graph to visually see where employees are over allocated.

 

The picture attached was made in excel but demonstrates what we would like to do in smartsheet. 

 

Any suggestions?

 

Steve 

Chris McKay

Hi Stevie,

Unfortunately, I cannot think of a way to accomplish exactly what you're looking to do in Smartsheet Graphs. But the following might be a potential workaround.

As per the images below, you'll need 3 additional columns in your resource data set apart from your Resource Allocation (which I've named Actual):

1. An Allocation % column with this formula:
   
   =IF(Actual1 < 1, Actual1, 1)
2. A Remaining % column with this formula:
   
   =IF([Allocation %]1 < 1, 1 - [Allocation %]1, 0)
3. An Over allocation % column with this formula:
   
   =IF(Actual1 > 1, ABS(1 - Actual1), 0)

This will look at your Actual allocation value (i.e. the real amount allocated) and if it is less than 100%, it will use that value in Allocation % and populate the Remaining % column with the deficit to total up to 100%. If Actual is more than 100%, it will mark the Allocation % column as 100% and move the remainder (i.e. the over allocation) to the Over allocation % column.

Now you just need to create a Stacked Bar graph and change the colours to what you feel is appropriate t create something that looks like the attached example graph.

I know it's not perfect, but I'm hoping it will be enough.

Kind regards,

Chris McKay

J. Craig Williams

Nice Chris.

Craig

Stevie Goodwin

Thanks Chris, 

I think that is probably my best bet at the moment. 

 

It looks good and I wonder if traffic light colours could work well for added impact.

 

Thanks for the suggestion

 

Steve

Chris McKay

My pleasure Stevie. Glad it provided something to use as a baseline.

I don't mind developing these sorts of workarounds, as I use them in my day to day too. Your requirement was certainly something that I could potentially require myself.

Kind regards,

Chris McKay