first
Efficient management of monthly rankings of requests handled by personnel in call centers, ticket systems, etc. is essential for the operation of any organization.
in the Smartsheet communityRecent inquiriesThen,A user creates a dashboard that shows the monthly count of requests processed and identifies the top 3 users who processed the most requests.I was looking for help.Additionally, this user had to consider both a main sheet and an archive sheet to store requests that were resolved more than 7 days ago, complicating matters.
This article presents a solution that combines formulas, functions, and effective data processing techniques to meet this challenge and generate metrics.
understand the challenge
The user's main goal was to develop a dashboard that provides the number of monthly requests and the top 3 users who have processed the most requests, based on data from both the main sheet and the archive sheet.To achieve this, the user had already created another metric sheet and referenced both sheets to calculate the request count for each month.
Create a request count metric
To calculate the total number of requests processed in a particular month,COUNTIFS functionwas used in two different formulas.The first formula was applied to the main sheet and the second formula was applied to the archive sheet. I created a formula that uses the IFERROR, MONTH, YEAR functions and compares the date of the request to the month and year specified in the metric sheet.
- =IFERROR(COUNTIFS({Email}, @cell = Name@row, {Date}, MONTH(@cell) = [This Month -1]#), “”)
- =IFERROR(COUNTIFS({Email of Archive}, @cell = Name@row, {Date of Archive}, MONTH(@cell) = [This Month -1]#), “”)
In the formula above, {Email}, {Date} are from the main sheet and {Email of Archive}, {Date of Archive} are from the archive sheet. (By the way, {Name} might have been easier to understand for the name of the reference.)
Handling user rankings
RANKEQ functionI ran into a problem when trying to determine the top 3 users based on number of requests using The RANKEQ function displays ties when multiple users have the same request count, eg XNUMX, XNUMX, XNUMX, XNUMX, XNUMX, in case of ties.
A user needed a solution that would not skip ranks and show all users with the same position.
To address this issue, we have proposed two formulas for handling ties: a skip-rank formula and a non-skip-rank formula.
As a solution,RANKEQ function for row numbers of users with the same rankusing,assigning ranks without skipping tiesI made itIn addition, we used the JOIN and COLLECT functions to display user names with the same rank.By implementing these formulas, we were able to get the top 3 user names and the corresponding number of requests. (In addition, in the case of the same rank, we decided that the one at the top of the name list will be ranked higher.)
- =IF(ISTEXT(Name@row), RANKEQ([#]@row, COLLECT([#]:[#], [This Month: Rank]:[This Month: Rank], [This Month: Rank]@row ), 1) – 1, “”)
It's a little difficult, so let me explain.
- The formula starts with an IF statement and checks if the cell in the Name@row column of the current row contains text (using the ISTEXT function).If the cell contains text, proceed to the next part of the formula.Otherwise, it returns an empty string ("").
- In the next part, we use the RANKEQ function to assign ranks to the values in the cells of the '#' column of the current row. The RANKEQ function compares a value to the range of values in the "#" column and returns its rank.The range to compare isCOLLECT function, from the "#" column, the condition range of [This Month: Rank]:[This Month: Rank] is changed to "This Month: Rank ” column ([This Month: Rank]@row).
- For example, in the image above, there are 1 items with the same rank of XNUMXth, but from the "#" column, the range containing those "#" values is obtained with the COLLECT function and ranked with the RANKEQ function. .Note that rank is calculated based on descending order, so the highest value ranks XNUMX.
- Finally, subtract 1 from the calculated rank, considering that the RANKEQ function assigns a rank of 1 to the highest value.With this adjustment, the highest value corresponds to rank 0, the second highest value corresponds to rank 2, and so on.
Automatic search for users
We also considered making it possible to automatically obtain the user name corresponding to the request even if the person in charge changed.So I came up with a solution that used enough pre-populated row numbers and the DISTINCT function. By applying the DISTINCT function to the main sheet, we were able to dynamically retrieve unique usernames, ensuring the accuracy of our measurements even as teams changed.
- =IFERROR(INDEX(DISTINCT({Name}), [#]@row), “”)
Summary
Effective monitoring of metrics such as ticketing systems is essential to maintaining efficient operations. By leveraging Smartsheet's capabilities and using the right formulas and functions, users can create comprehensive dashboards that provide valuable insights.By taking on the challenge of calculating the top 3 users based on the number of requests from both the main sheet and the archive sheet, we can better understand individual performance and optimize our processes.