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
-
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 namedActual):
- AnAllocation %column with this formula:
=IF(Actual1 < 1, Actual1, 1) - ARemaining %column with this formula:
=IF([Allocation %]1 < 1, 1 - [Allocation %]1, 0) - AnOver allocation %column with this formula:
=IF(Actual1 > 1, ABS(1 - Actual1), 0)
This will look at yourActualallocation value (i.e. the real amount allocated) and if it is less than 100%, it will use that value inAllocation %and populate theRemaining %column with the deficit to total up to 100%. IfActualis more than 100%, it will mark theAllocation %column as 100% and move the remainder (i.e. the over allocation) to theOver allocation %列。
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
- AnAllocation %column with this formula:
-
J. Craig Williams ✭✭✭✭✭✭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
-
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
Categories
It depends on your automation settings. If you set it to unrestricted, you can send to an email address in a cell without giving them access to the sheet. You can also specify which cells are provided in the update request within the automation builder.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106840,"type":"question","name":"Report not showing all rows that match the filter parameters","excerpt":"Hello! I have a sheet that has 3424 rows with a specific Due Date. When I look at that filter option in the report, I see that it sees 3424 for that field. However, when I add that parameter, only 2500 rows display. I can't find a row limit to reports that would cause this. Is there a row limit to reports that I'm unaware…","categoryID":321,"dateInserted":"2023-06-23T16:50:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T17:36:10+00:00","insertUserID":74136,"insertUser":{"userID":74136,"name":"asimcock","url":"https:\/\/community.smartsheet.com\/profile\/asimcock","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T17:45:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":41,"score":null,"hot":3375083206,"url":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106840\/report-not-showing-all-rows-that-match-the-filter-parameters","format":"Rich","tagIDs":[265],"lastPost":{"discussionID":106840,"commentID":382043,"name":"Re: Report not showing all rows that match the filter parameters","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382043#Comment_382043","dateInserted":"2023-06-23T17:36:10+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T19:11:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T17:47:12+00:00","dateAnswered":"2023-06-23T17:02:03+00:00","acceptedAnswers":[{"commentID":382031,"body":"
See this article. There is a 2500 row limit for reports.<\/p>
