Orderable Charts
Good morning, I've done several searches here but now I'm going in circles.
I have a sheet with records of usage of certain scripts we have. Each row has the user name, the script they used and the time they saved by using each script.
My goal is to create a dashboard with two bar charts:
- user name vs total time savings (ordered by highest time savings)
- most used scripts (ordered highest count)
The charts are ready but the bars can't be reordered which makes them pointless. Also, the reports they've been created from can't be ordered based on the sums, which means this approach didn't work. I then tried using a blank sheet with formulas showing the total savings per user and create the chart on top of that but now my chart won't update when a new user shows up in the sheet.
Can anyone see a way I can achieve that? Thank you :-)
Best Answers
-
AravindGP ✭✭✭✭
I understand. Unfortunately, there is no other option that I see. One thing to make it easier for you to add the name is to have a helper column in the source sheet with a formula to identify duplicates. If there is a record with no duplicate (i.e., a new name added), you can get notified through a workflow and use that to add the name to your metric sheet.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
AravindGP ✭✭✭✭
You can use the below formula in a helper checkbox column. Using this formula, the helper column you create will be checked when there is no duplicate entry. You can then use the helper column being checked as the trigger to notify yourself to create the name in the metrics sheet.
=IF(COUNTIF([User Name]:[User Name], [User Name]@row)>1, 0, 1)
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
Genevieve P. Employee Admin尝试添加一个@cellreference in your formula, this can sometimes help with COUNTIFS looking at a number/text value:
=IF(COUNTIF([User Name]:[User Name],@cell =[User Name]@row)>1, 0, 1)
Let us know if that helped!
Cheers,
Genevieve
Answers
-
AravindGP ✭✭✭✭
Hi There,
I assume the sheet you've that is collecting the data is similar to the screenshot you have added. This means that you can't really get the number of times a script is used without the help of sheet summary or a separate metrics sheet to count the number of times a script has been used. In the metrics sheet, you can add in the unique users and do a sumif formula to get the time saved across all scripts. When you have a new user added to the source sheet, all you have to do is add the name of the new user to your metrics sheet and it will auto do the formula to get the time saved sum for that user. You can have the column of time saved in your metric sheet sorted to be highest to lowest and then modify your chart to have the values listed in the desired order.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
Floretti ✭
Thanks for the reply, Aravind. That definitely looks like an option I can use. Do you see any way to avoid the manual intervention when another user is added?
We currently have a large number of users and high turnover, which means I'll have new names turn up very often and I'm expecting it to be hard to spot the missing ones amongst all the names.
-
AravindGP ✭✭✭✭
I understand. Unfortunately, there is no other option that I see. One thing to make it easier for you to add the name is to have a helper column in the source sheet with a formula to identify duplicates. If there is a record with no duplicate (i.e., a new name added), you can get notified through a workflow and use that to add the name to your metric sheet.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
Floretti ✭
That could work, thanks heaps Aravind. I'll try and implement that.
-
Floretti ✭
@AravindGPI realise it is off topic but any tips on how to achieve the formula to identify duplicates in the helper sheet? The rest is sorted as you suggested but I have no idea how to implement that part. Thank you.
-
AravindGP ✭✭✭✭
You can use the below formula in a helper checkbox column. Using this formula, the helper column you create will be checked when there is no duplicate entry. You can then use the helper column being checked as the trigger to notify yourself to create the name in the metrics sheet.
=IF(COUNTIF([User Name]:[User Name], [User Name]@row)>1, 0, 1)
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
Floretti ✭
Thanks so much but something very odd happened. It's considering the user 1104825 as a new user. Note that the user name is added via API and the user cell is fed the string "1104825", which ends up as '1104825 in Smartsheet. I wonder if that's what is causing the issue.
-
Genevieve P. Employee Admin
尝试添加一个@cellreference in your formula, this can sometimes help with COUNTIFS looking at a number/text value:
=IF(COUNTIF([User Name]:[User Name],@cell =[User Name]@row)>1, 0, 1)
Let us know if that helped!
Cheers,
Genevieve
-
Floretti ✭
@Genevieve P.Yep, that did the job, thank yoooou! :-)
@AravindGPThanks again, I very much appreciate the help.
Categories
<\/p>
If they are licensed on another account, they would need to either be first removed from that account (can cause some logistical headaches) or added as an unlicensed user on your account.<\/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":543,"urlcode":"licensed-users","name":"licensed users"}]},{"discussionID":108737,"type":"question","name":"Visibility on Form Submission","excerpt":"Is there a way to increase visibility for requestors on their submitted requests? Like is it possible to send the requestors all of the requests they've submitted so they can see what they've requested, date, description, etc.","snippet":"Is there a way to increase visibility for requestors on their submitted requests? Like is it possible to send the requestors all of the requests they've submitted so they can see…","categoryID":321,"dateInserted":"2023-08-09T15:23:45+00:00","dateUpdated":"2023-08-09T15:24:46+00:00","dateLastComment":"2023-08-09T18:04:13+00:00","insertUserID":164850,"insertUser":{"userID":164850,"name":"ocanel","title":"Analyst","url":"https:\/\/community.smartsheet.com\/profile\/ocanel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-09T17:57:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":46,"score":null,"hot":3383201878,"url":"https:\/\/community.smartsheet.com\/discussion\/108737\/visibility-on-form-submission","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108737\/visibility-on-form-submission","format":"Rich","tagIDs":[204,324,334,440,448],"lastPost":{"discussionID":108737,"commentID":389865,"name":"Re: Visibility on Form Submission","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389865#Comment_389865","dateInserted":"2023-08-09T18:04:13+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+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-08-09T17:57:54+00:00","dateAnswered":"2023-08-09T15:59:17+00:00","acceptedAnswers":[{"commentID":389837,"body":"
