New Graph functionality

Stevie Goodwin
Stevie Goodwin ✭
edited 06/14/22 inAdd Ons and Integrations

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

Chart Example.png

Tags:
· Share on Facebook Share on Twitter

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭
    edited 04/16/18

    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):

    1. AnAllocation %column with this formula:

      =IF(Actual1 < 1, Actual1, 1)
    2. ARemaining %column with this formula:

      =IF([Allocation %]1 < 1, 1 - [Allocation %]1, 0)
    3. 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

    Screen Shot 2018-04-16 at 10.00.56 pm.png

    Screen Shot 2018-04-16 at 10.00.38 pm.png

    · Share on Facebook Share on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Nice Chris.

    Craig

    · Share on Facebook Share on Twitter
  • Stevie Goodwin
    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

    · Share on Facebook Share on Twitter
  • Chris McKay
    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

    · Share on Facebook Share on Twitter

Categories

Hi @Darla Brown<\/a> <\/p>

I hope you're well and safe!<\/p>

Has anything in the sheet changed? Any formulas in the cells? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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":107125,"type":"question","name":"How to hide certain columns to certain poeple, while unhide to the rest","excerpt":"Hi Dear Community I am sharing my sheet to 2 groups of poeple, for group A they can see the entire sheet, but for group B I would like they to see only part of the columns. Both groups can edit what they can see in the sheet. Is there a way to acheive that? Thanks!","snippet":"Hi Dear Community I am sharing my sheet to 2 groups of poeple, for group A they can see the entire sheet, but for group B I would like they to see only part of the columns. Both…","categoryID":321,"dateInserted":"2023-06-30T09:24:53+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T12:44:32+00:00","insertUserID":162810,"insertUser":{"userID":162810,"name":"流风回雪","url":"https:\/\/community.smartsheet.com\/profile\/%E6%B5%81%E9%A3%8E%E5%9B%9E%E9%9B%AA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T11:58:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-30T15:26:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":49,"score":null,"hot":3376248565,"url":"https:\/\/community.smartsheet.com\/discussion\/107125\/how-to-hide-certain-columns-to-certain-poeple-while-unhide-to-the-rest","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107125\/how-to-hide-certain-columns-to-certain-poeple-while-unhide-to-the-rest","format":"Rich","lastPost":{"discussionID":107125,"commentID":383311,"name":"Re: How to hide certain columns to certain poeple, while unhide to the rest","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383311#Comment_383311","dateInserted":"2023-06-30T12:44:32+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-30T15:26:57+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-30T11:59:52+00:00","dateAnswered":"2023-06-30T10:33:48+00:00","acceptedAnswers":[{"commentID":383272,"body":"

Hi Dear Andrée Starå<\/strong><\/a><\/p>

Thank you so much for your solution!<\/p>

It can work, but more like a compromise way. Editing on a report is less convenient.<\/p>

It would be better for me to set access right to each column.<\/p>

But that can also work for now, thanks a lot.<\/p>

By the way are you a BOT?<\/p>"},{"commentID":383283,"body":"

@流风回雪<\/a> <\/p>

Excellent!<\/p>

Happy to help!<\/p>

I agree. Great idea! That would be a great addition to Smartsheet features.<\/p>

Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p>

Haha!, No, I'm not a bot! 🤣<\/span><\/p>

✅Remember! <\/strong>Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up\/Awesome or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":107109,"type":"question","name":"Bridge Workflow - Runs with no Errors but doesn't copy data to column. Am I missing modules?","excerpt":"I have a Bridge Workflow that is set up to copy data from a row\/column to the same row\/different column. The workflow runs with all module steps \"SUCCEDED\". However, the end result is not copied in my Smartsheet's target column. Its blank. Am I missing a module or module steps to complete the copy routine? -- Here's a…","snippet":"I have a Bridge Workflow that is set up to copy data from a row\/column to the same row\/different column. The workflow runs with all module steps \"SUCCEDED\". However, the end…","categoryID":343,"dateInserted":"2023-06-29T21:27:54+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T15:01:40+00:00","insertUserID":148855,"insertUser":{"userID":148855,"name":"cabbsman","url":"https:\/\/community.smartsheet.com\/profile\/cabbsman","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!88cq90f5xTs!8sDpd7uL9Ps!kj4X4avpgGN","dateLastActive":"2023-06-30T15:02:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-30T16:28:26+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3376213174,"url":"https:\/\/community.smartsheet.com\/discussion\/107109\/bridge-workflow-runs-with-no-errors-but-doesnt-copy-data-to-column-am-i-missing-modules","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107109\/bridge-workflow-runs-with-no-errors-but-doesnt-copy-data-to-column-am-i-missing-modules","format":"Rich","tagIDs":[369],"lastPost":{"discussionID":107109,"commentID":383333,"name":"Re: Bridge Workflow - Runs with no Errors but doesn't copy data to column. Am I missing modules?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383333#Comment_383333","dateInserted":"2023-06-30T15:01:40+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-30T16:28:26+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/G9744765U09U\/screenshot-28106-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot (106).png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T14:52:58+00:00","dateAnswered":"2023-06-30T11:53:00+00:00","acceptedAnswers":[{"commentID":383292,"body":"

Hi @cabbsman<\/a> <\/p>

It looks like you're using the Update Column module, which would update column properties<\/em> (e.g. what elements appear in a dropdown list to select, or changing it to a Contact type of column). <\/p>

Instead, use <\/strong>Update Row<\/strong> (see here)<\/a><\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

You'll want to add the Data Reference from your Get Row call to input the Sheet<\/strong> and the same Row ID<\/strong>.<\/p>

Then under Cells<\/strong>, use the Key to identify the column and the Value can be pulled from the Run Log of your Get Row module. <\/p>


<\/p>

Let me know if that makes sense and works for you!<\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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":[{"tagID":369,"urlcode":"bridge-by-smartsheet","name":"Bridge"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5434,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet

  • Dynamic View not allowing editable field

    Hi, I have 2 fields in my Dynamic View that are built exactly the same way. One is showing as Read Only and the other Required. They both need to be Required. Anyone else having this issue? I just built this last week and was working correctly until today.

  • How to hide certain columns to certain poeple, while unhide to the rest

    Hi Dear Community I am sharing my sheet to 2 groups of poeple, for group A they can see the entire sheet, but for group B I would like they to see only part of the columns. Both groups can edit what they can see in the sheet. Is there a way to acheive that? Thanks!

  • Bridge Workflow - Runs with no Errors but doesn't copy data to column. Am I missing modules?

    I have a Bridge Workflow that is set up to copy data from a row/column to the same row/different column. The workflow runs with all module steps "SUCCEDED". However, the end result is not copied in my Smartsheet's target column. Its blank. Am I missing a module or module steps to complete the copy routine? -- Here's a…