Top 3 Users based on Count of Requests from both Main and Archive Smartsheet
Good morning,
I've been battling this all day yesterday and hoping someone could help.
To provide a bit of the background, our team is using Smartsheet as a ticketing system and there are ~20 users who are assigned to each request. I'm trying to develop a dashboard that would give me the # of requests processed monthly, along with the top 3 users that processed the most requests, and the count of the requests for those top 3 users. Because we process a lot of requests, we now have an archive sheet on top of the main sheet to store the requests if the resolved date is greater than 7 days. So it is possible that main sheet has a mixture of both resolved and open requests.
Based on the existing knowledge shared on this Community I was able to create a separate metrics sheet that references both sheets to get the count for each month.
As you can see, to get the total # of requests processed, I am adding the count from the Main sheet (Row6) and the Archive sheet (Row7).
For the column of "This Month" I have the following formula for Row 6
=COUNTIFS({Main Closed Date}, IFERROR(MONTH(@cell), 0) = [This month]2, {Main Closed Date}, IFERROR(YEAR(@cell), 0) = [This month]1)
For the column of "This Month" I have the following formula for Row 7
=COUNTIFS({Archive Closed Date}, IFERROR(MONTH(@cell), 0) = [This month]2, {Archive Closed Date}, IFERROR(YEAR(@cell), 0) = [This month]1)
Now the problem I'm trying to solve is to get the unique user name based on the count of requests processed (Row 9-11). In case of multiple users with the same count, it wouldn't matter who gets listed first because I am planning to provide the count of processed tickets after the names. Once I can get the name of the top 3 users, I believe I could add another condition to COUNTIFS to get the count by user names.
Where I feel like it gets complicated is that there are 2 separate sheets I'm trying to work it out and the fact that I am using this sheet to feed a Dashboard since the report will not work for Dashboards.
Any help would be appreciated. Thank you!
Best Answers
-
Hey @ctakeda
Charts can use Reports as a source! 🙂
The difference is that a Chart will look at the entirety of a Report OR at the first Grouping/Summary level, depending on how you display your data.
However if you're looking to just filter by "top 3" then you're right, you'd need to have something in your underlying source sheet that identifies the top 3 in order for the Report to filter correctly (versus showing all data).
What I would do is have 20 rows at the bottom that count each individual's row could across both sheets per-month.
=COUNTIFS({First Sheet Name Column}, Name@row, {First Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {First Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1) + COUNTIFS({Second Sheet Name}, Name@row, {Second Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {Second Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1)
Or in your case, "Primary Column":
=COUNTIFS({First Sheet Name Column}, [Primary Column]@row, {First Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {First Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1) + COUNTIFS({Second Sheet Name Column}, [Primary Column]@row, {Second Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {Second Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1)
Then once you have a COUNT, you can use the LARGE Function to identify the highest number, 2nd highest number, 3rd highest number, etc:
=JOIN(COLLECT([Primary Column]9:[Primary Column]11, [This Month]9:[This Month]11, LARGE([This Month]9:[This Month]11, 3)), ", ")
^Notice in this formula I have a 3 in the Large function because I'm looking for the 3rd largest. In my example I'm also using JOIN(COLLECT in case there's a tie:
Cheers,
Genevieve
-
Hi @ctakeda & @Genevieve P.
There is a high need for a dashboard to show the monthly ranking of the number of processes handled by the person in charge.
Based on your comments, I created a dashboard showing how to use the functions, etc.The points I tried to make are,
First, I created two groups of formulas to process ties, one is to skip and rank, and the other is not to skip.
Precisely, using the RANKEQ() function, we came up with a method to add a rank without skipping in the case of a tie ranking and a way to display the name of the person with the same position using JOIN(COLLECT()) without skipping.
Second, we devised a way to automatically capture the person in charge used to calculate the ranking without using a report, even if there are changes in the people in charge.
Specifically, we create a sufficient number of row numbers in advance and use the DISTINCT() function to retrieve unique names from the main sheet containing the names of the persons in charge.
Please take a look at the URL below.
https://app.smartsheet.com/b/publish?EQBCT=54b3f97b13664292bb8e019a0881f0d2
Answers
-
Actually, a report would work in the case depending on what you are wanting to show. If you are just wanting a little metrics widget, then yes, a report won't work, but you could show the report and then have filters and summaries set to show the three top users. You can have a report connected to multiple sheets as well.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
Hi Jonathan, thanks for the comment! I would love to use the report but I'm trying to use this information to create a bar plot in a dashboard. Unfortunately dashboards cannot reference data from a report.
-
Hey @ctakeda
Charts can use Reports as a source! 🙂
The difference is that a Chart will look at the entirety of a Report OR at the first Grouping/Summary level, depending on how you display your data.
However if you're looking to just filter by "top 3" then you're right, you'd need to have something in your underlying source sheet that identifies the top 3 in order for the Report to filter correctly (versus showing all data).
What I would do is have 20 rows at the bottom that count each individual's row could across both sheets per-month.
=COUNTIFS({First Sheet Name Column}, Name@row, {First Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {First Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1) + COUNTIFS({Second Sheet Name}, Name@row, {Second Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {Second Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1)
Or in your case, "Primary Column":
=COUNTIFS({First Sheet Name Column}, [Primary Column]@row, {First Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {First Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1) + COUNTIFS({Second Sheet Name Column}, [Primary Column]@row, {Second Sheet Date}, IFERROR(MONTH(@cell), 0) = [This Month]$2, {Second Sheet Date}, IFERROR(YEAR(@cell), 0) = [This Month]$1)
Then once you have a COUNT, you can use the LARGE Function to identify the highest number, 2nd highest number, 3rd highest number, etc:
=JOIN(COLLECT([Primary Column]9:[Primary Column]11, [This Month]9:[This Month]11, LARGE([This Month]9:[This Month]11, 3)), ", ")
^Notice in this formula I have a 3 in the Large function because I'm looking for the 3rd largest. In my example I'm also using JOIN(COLLECT in case there's a tie:
Cheers,
Genevieve
-
Hi Genevieve, thanks for your detailed explanation! I will try your method and see if it works. If I do not have a set list of users, meaning that there may be new users that did not exist in previous months, is there a way to list them instead of hard-coding user names?
-
Hi @ctakeda
If you don't have a list of set users, this is where I'd suggest using a Report to Group by the Name and the Summarize to count the number of rows across all your sheets.
Charts can then graphs based on that top Grouped/Summarized row so you will quickly see the top submitters (along with all other submitters for that month).
Reports will automatically bring in new information, whereas a formula will need you to tell it what to count.
See: Redesigned Reports with Grouping and Summary Functions
Cheers,
Genevieve
-
Hi @ctakeda & @Genevieve P.
There is a high need for a dashboard to show the monthly ranking of the number of processes handled by the person in charge.
Based on your comments, I created a dashboard showing how to use the functions, etc.The points I tried to make are,
First, I created two groups of formulas to process ties, one is to skip and rank, and the other is not to skip.
Precisely, using the RANKEQ() function, we came up with a method to add a rank without skipping in the case of a tie ranking and a way to display the name of the person with the same position using JOIN(COLLECT()) without skipping.
Second, we devised a way to automatically capture the person in charge used to calculate the ranking without using a report, even if there are changes in the people in charge.
Specifically, we create a sufficient number of row numbers in advance and use the DISTINCT() function to retrieve unique names from the main sheet containing the names of the persons in charge.
Please take a look at the URL below.
https://app.smartsheet.com/b/publish?EQBCT=54b3f97b13664292bb8e019a0881f0d2
-
Thank you so much, @jmyzk_cloudsmart_jp--especially for the example and the dashboard. It is very similar to how I wanted to visualize our data. Super helpful!! Have a great one!!!
Help Article Resources
Categories
Check out the Formula Handbook template!
