How can I change the spacing of tick marks on the x axis as one per day is too many?


每日测量显示太多意图d the dates. How can I fix this to have the chart adjust to the number of data points?

image.png


Best Answers

  • Samuel Mueller
    Samuel Mueller ✭✭✭✭✭✭
    Answer ✓

    @m.loefflerSo what you would do here is make a helper column that has a date in every 4 rows, and leave the others blank. Then make that your farther left column. When you add the chart and you click the option that says use the first column as data labels, it should be spaced out more.

    If that doesn't make sense, I can send an example.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/29/23 Answer ✓

    When doing something like this, I like to create a table that has every day listed (even if there were no results). The absolute cleanest way I have found is to create a Sheet Summary field to pull in the start date using a MIN function. Then I create a text/number column called "Number" and start with 0 (zero) in the top row and go to however many numbers I think I am going to need. In my date column I use something like this as a column formula:

    =IF([Start Date]# +[email protected]<= TODAY(), [Start Date]# +[email protected])


    Now my dates will automatically adjust based on today's date. You can use an INDEX/MATCH or some other function as needed to bring in my totals being sure to leave any dates that don't have data blank (not zero) with an IFERROR or some other similar approach. From there we have our "Labels" column as previously described.


    Finally we create a report that only pulls in rows where my date column is not blank. We only include the Labels column and the calc column and then use that to generate your graph.


    Now you have something where the labels are evenly spaced and will also automatically grow as time goes on. Of course once you get enough weeks populating the chart, you may need to adjust the logic for which dates are pulled into your "Labels" column, but that is relatively minor maintenance.


    The chart below actually covers 365 days (there are some days where there was no data recorded).

    image.png


    thinkspi.com

Answers

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭

    This may or may not help but as far as I know you can adjust the widget size and get some more space between.

    Ken Armstrong

    Sr System Analyst, Carelon Medical Benefits Management

    Certified Smartsheet Administrator

    Be Firm! Be Fair! Be Friendly! Be Honest!!!

  • Ken, Thanks for the idea, but it only buys a bit of time as the data will continue to crowd the axis. I know in Excel you can adjust the ticks and they automatically adjust as well. I am hopeful that someone has a trick for this....

  • Samuel Mueller
    Samuel Mueller ✭✭✭✭✭✭
    Answer ✓

    @m.loefflerSo what you would do here is make a helper column that has a date in every 4 rows, and leave the others blank. Then make that your farther left column. When you add the chart and you click the option that says use the first column as data labels, it should be spaced out more.

    If that doesn't make sense, I can send an example.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Depending on how many data points you have, I usually use a formula that will pull the 1st and 15th into the helper column mentioned by@Samuel Mueller(I usually call it "Labels").


    =IF(OR(DAY([Date Column]@row) = 1, DAY([Date Column]@row) = 15)), [Date Column]@row + "")

    thinkspi.com

  • Paul,

    Interesting solution, I used your concept to pull the Mondays only for the data labels. Seems a bit better, but clean. It seems the testing is not every monday so it looks a bit off. Thanks for the idea.


    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/29/23 Answer ✓

    When doing something like this, I like to create a table that has every day listed (even if there were no results). The absolute cleanest way I have found is to create a Sheet Summary field to pull in the start date using a MIN function. Then I create a text/number column called "Number" and start with 0 (zero) in the top row and go to however many numbers I think I am going to need. In my date column I use something like this as a column formula:

    =IF([Start Date]# +[email protected]<= TODAY(), [Start Date]# +[email protected])


    Now my dates will automatically adjust based on today's date. You can use an INDEX/MATCH or some other function as needed to bring in my totals being sure to leave any dates that don't have data blank (not zero) with an IFERROR or some other similar approach. From there we have our "Labels" column as previously described.


    Finally we create a report that only pulls in rows where my date column is not blank. We only include the Labels column and the calc column and then use that to generate your graph.


    Now you have something where the labels are evenly spaced and will also automatically grow as time goes on. Of course once you get enough weeks populating the chart, you may need to adjust the logic for which dates are pulled into your "Labels" column, but that is relatively minor maintenance.


    The chart below actually covers 365 days (there are some days where there was no data recorded).

    image.png


    thinkspi.com

The side by side option means the link\/icon is on the side of the text (in your case the link\/icon is next to aaaa or bbbb). The stacked option is different because the link\/icon is above the text (stacked above).<\/p>

If you're looking to have the widgets themselves side by side, then you can set up multiple, individual widgets and put them next to each other.<\/p>

So \"side by side\" is better described as \"link\/icon and text are side by side!\"<\/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":[{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":533,"urlcode":"web-content-widget","name":"web content widget"}]},{"discussionID":107953,"type":"question","name":"Adding name to automated workflow message","excerpt":"I created a workflow that notifies an individual when his\/her license is about to expire. There is a notification that goes out at 30 days that also notifies the individual's manager. The workflow runs fine, however, there is no way for the manager to tell which one of his\/her staff the message was intended for AND in some…","snippet":"I created a workflow that notifies an individual when his\/her license is about to expire. There is a notification that goes out at 30 days that also notifies the individual's…","categoryID":321,"dateInserted":"2023-07-23T17:22:23+00:00","dateUpdated":null,"dateLastComment":"2023-07-23T18:58:20+00:00","insertUserID":114017,"insertUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-07-23T18:56:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":114017,"lastUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-07-23T18:56:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3380272843,"url":"https:\/\/community.smartsheet.com\/discussion\/107953\/adding-name-to-automated-workflow-message","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107953\/adding-name-to-automated-workflow-message","format":"Rich","lastPost":{"discussionID":107953,"commentID":386567,"name":"Re: Adding name to automated workflow message","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386567#Comment_386567","dateInserted":"2023-07-23T18:58:20+00:00","insertUserID":114017,"insertUser":{"userID":114017,"name":"Jacqueline Ennis","url":"https:\/\/community.smartsheet.com\/profile\/Jacqueline%20Ennis","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZA0IOt6rrnETj3_ATzn0g7JjbJTEyOyIfrNKKb=s96-c","dateLastActive":"2023-07-23T18:56:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T09:39:01+00:00","dateAnswered":"2023-07-23T18:44:43+00:00","acceptedAnswers":[{"commentID":386564,"body":"

In the subject and\/or body of any notification, you can add data from the row itself using double-curly brackets and the column name. So this might mean your message looks something like this:<\/p>

The following individual's license expires in 30 days:<\/p>

{{Contact Name}} (where \"contact name\" is the name of your column with the person's name in it)<\/p>

This merges information from the row into the body of the email notification. See screenshot for alert notification - CUSTOMIZE MESSAGE, and the instructions embedded in Smartsheet that outline how to use this feature.<\/p>

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


<\/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":107937,"type":"question","name":"True\/False formula?","excerpt":"I'm trying to figure out a formula to note a \"1\" in the cell if the information in the cell next to it is a certain value, any other value needs to show a \"0\". Is this a \"true\/false\" formula? I figured this out: =IF([Question 1]@row = \"2. I should avoid using the electronic device during the initial connection; however,…","snippet":"I'm trying to figure out a formula to note a \"1\" in the cell if the information in the cell next to it is a certain value, any other value needs to show a \"0\". Is this a…","categoryID":321,"dateInserted":"2023-07-21T20:52:02+00:00","dateUpdated":"2023-07-21T20:53:35+00:00","dateLastComment":"2023-07-21T21:06:17+00:00","insertUserID":159988,"insertUser":{"userID":159988,"name":"Jillian M","url":"https:\/\/community.smartsheet.com\/profile\/Jillian%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bR_Ym_nhTvw!oRnZKN3xkb4!GGy8kNscpSH","dateLastActive":"2023-07-21T21:16:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":159988,"lastUserID":159988,"lastUser":{"userID":159988,"name":"Jillian M","url":"https:\/\/community.smartsheet.com\/profile\/Jillian%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bR_Ym_nhTvw!oRnZKN3xkb4!GGy8kNscpSH","dateLastActive":"2023-07-21T21:16:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3379948099,"url":"https:\/\/community.smartsheet.com\/discussion\/107937\/true-false-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107937\/true-false-formula","format":"Rich","lastPost":{"discussionID":107937,"commentID":386520,"name":"Re: True\/False formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386520#Comment_386520","dateInserted":"2023-07-21T21:06:17+00:00","insertUserID":159988,"insertUser":{"userID":159988,"name":"Jillian M","url":"https:\/\/community.smartsheet.com\/profile\/Jillian%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bR_Ym_nhTvw!oRnZKN3xkb4!GGy8kNscpSH","dateLastActive":"2023-07-21T21:16:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-21T20:55:47+00:00","dateAnswered":"2023-07-21T20:53:49+00:00","acceptedAnswers":[{"commentID":386517,"body":"

@Jillian M<\/a> an IF statement will do what you need.<\/p>

=if([Column Name]@row=\"Value\",1,0)<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4874,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics