New Graph functionality
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
Comments
-
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):
- An Allocation % column with this formula:
=IF(Actual1 < 1, Actual1, 1) - A Remaining % column with this formula:
=IF([Allocation %]1 < 1, 1 - [Allocation %]1, 0) - 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
- An Allocation % column with this formula:
-
Nice Chris.
Craig
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives