Custom Sorting for Donut Chart Status Labels in Smartsheet

System

This discussion was created from comments split from: Grouping without sorting by that grouping?.

Griff D

I am having a similar issue with trying to create a chart (Donut Chart) showing the number of projects "Not Started "Started" "On Hold" and "Done". I do not want to put a number in front of all of the statuses because that would just look stupid. I tried creating a status order column, and sorting on that but with the way that Smartsheets forces a sort on the column you are grouping, my chart is in the order Done, Not Started, On Hold, then Not Started which is random with respect to the workflow. I did find a way to get the data in the right order by grouping on Status order then Status but the issue is that you cannot select which column to make the series label so my chart has the Status Order numbers rather than the Status names.

Paul Newcome

@Griff D You will need to use a second sheet and apply formulas with cross sheet references. You can put the labels in whatever order you want that way.

Griff D

@Paul Newcome That is how I am currently doing it, but it is not convenient for reasons I will not go into here. Seems like a workaround that can be handled better by tweaking the way the reports are charted.

Paul Newcome

I'd be happy to brainstorm with you on ways to make the sheet method more convenient if you'd like to see if there are potentially other options and/or methods.

Griff D

@Paul Newcome the issues with a Metric Sheet is that I need to manually add Employees when a new employee is added. Adding an employee is not hard, it is just a matter of remembering I have to do that after months or years.

Sheet Design:
Columns for Employee Name, and several columns for the number of tasks "To Do", "In Progress", "Done".

Paul Newcome

@Griff D There are a number of ways to design a metrics sheet so that you do not have to manually add anyone. My preferred method is to manually enter the numbers 1 through however many you want/need plus some buffer for expansion in a column (called "Number" in this example). Then in another text/number column (called "Employee" in this example), you would use

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Employee Column}, {Source Sheet Employee Column}, @cell <> "")), Number@row), "")

Then in additional columns you can run your regular metrics per person. Finally you would create a report that pulls from the metrics sheet and is filter to show rows where Employee is not blank. This report would feed your chart to keep it dynamically showing everyone listed in the source sheet without manual intervention.

Griff D

Interesting, I will give that a shot, and look into the DISTINCT function since I have no experience with that.

Thank you