What is the best way to display the Duration column on a dashboard when I have over 1500 rows ?
Hello here,
I would like to display in a dashboard the duration column which is the time that the task took to be completed. What is the best way to display the Duration column on a dashboard, when I have over 1500 rows in my sheet? Is there a way to break it down by ticket type and take the average ?Could you please help me find the best way to show this?
I tried to do this as below, but not sure what these numbers really indicate. So ideally I would like to use the Duration Column (as shown in the picture above) as it is more clear.
Looking forward for some help here.
Thank you!
Answers
-
Hey @Christiana Gkini ,
The average in the report will show average days in the column. you can create a group average on the duration column.
Did you add the Smartsheet duration column or is that one you create yourself?
I would reccomend using the Smartsheet column - right click a column and go to Edit Project Settings. there you can add the duration and Predecessors columns that will make your life easier and will be able to average in the report as well.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hi Itai,
Thanks for getting back to me. I had to create it myself as things were more complicated.
However if I try what you said this is how it shows. 1st picture if I break the duration in Days, hours and minutes and 2nd picture when I try to get the average form the Duration column that I want it gives the notification NaN. Any chance you know how I can get the average in the Duration column and the best way to display this in the dashboard?
-
@Christiana Gkini I think you need a helper column that combines days hours and minutes, then you could just average that column and group by type.
Something like a helper column named "Total Duration" with the formula
=days@row +hours@row/24 + minutes@row/1440
This will give you total duration in days, and if you average that in a report you would get average duration grouped by whatever group you choose.
-
Hi Samuel,
Thanks for your advise.
I did the helper column and tried to group with the report as you said. However this appears so big in my dashboard.
I have decided to show only some of the ticket types in a sheet and with an average formula to get the average duration for the ticket types I want only. I have tried a couple of average formulas, but none of them is working. Could you show me which formula is the best in the case?
Looking forward for some help.
Thank you!
-
@Christiana Gkini One option would be to just filter that same report only to the ticket types you want to see.
Otherwise, your formula should just be
=iferror(averageif({copy of snow range 3},[ticket type]@row, {duration}),0)
-
Example of creating a report filter for ticket type
-
For the formula option make sure you get the cross reference names right
=iferror(averageif({*ticket type column*},[ticket type]@row, {*duration helper column*}),0)
-
Hi Samuel,
It worked thank you very much!
One last question please, this number indicates the average days, right?
Thank you!
-
@Christiana Gkini if "{copy of snow Q1 Range 2}" is referencing the helper column you created with days hours and minutes, then yes it should be the average of days corresponding to the matching ticket types.
-
Thank you very much for your help Samuel!
Have a great weekend!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives