0

When using the summary function "Group" in Saved Searches (SS), is it possible to show the total count inline or even use it inline? For example, I have a SS that counts the number of cases closed in a certain date range and it groups by the assigned employee and the total is listed at the bottom, as per usual. However, when trying to calculate the percent of the total each employee closed, they all show as 100%.

Here is a picture of the results and I have also added the formulas I am currently using. Here is what it looks like when I'm editing the search. The right most columns were my attempt at getting the total inline.

I'm fairly certain this is because I am grouping by the employees (or else there would be almost 3k lines in the report), but I don't think there is a better way to solve that problem other than by grouping by the employee.

We have tried doing an actual report in NetSuite (as opposed to saved search), however, the report times out quickly and we are hoping for a quicker solution. We also considered a KPI scorecard, but the issue would be that we would need to make a SS for each employee which isn't a good long-term solution due to team changes.

Is there a way of calculating the percent of the total when using grouping? Sorry for the long post, I was trying to be as descriptive as possible. The goal is to see how much (percent wise) each employee contributed to the total cases closed.

  • You should edit your post to include the actual code you're using, not a screenshot of it. – Jdg50 Feb 06 '19 at 14:16
  • Are you trying to calculate the % of cases in a time range that are closed per employee e.g. (cases closed/total cases) or the employee's pct of total cased closed? – bknights Feb 06 '19 at 17:18
  • @bknights Sorry, to clarify, the search criteria already handles the date range so I'm trying to see what percent each employee contributed. So if 2.8k is the total amount of cases closed and the first person closed 97, I'm looking to do 97/2847. –  Feb 06 '19 at 19:59

1 Answers1

0

Take your SUM column that counts the closed cases. duplicate it but add the function % of total

So 

 | Field          | Summary Type | Function   | Formula                                              |
 | -----          | ------------ | --------   | ---------------------------------------------------- |
 |Formula Numeric | Sum          |            |  Case When {status} like 'Closed%' then 1 else 0 End |
 |Formula Numeric | Sum          | % of Total |  Case When {status} like 'Closed%' then 1 else 0 End |
bknights
  • 14,408
  • 2
  • 18
  • 31
  • Like so? SUM (CASE WHEN {status} LIKE 'Closed%' THEN 1 ELSE 0 END) / COUNT({status}) When doing this, I still get 100% as the result every time. I saw you had answered similar questions to mine and tried the answer in the other questions ([like this one](https://stackoverflow.com/questions/34020104/formula-in-netsuite-saved-search/34048645#34048645)), but it still shows 100%. ([pic of results](https://i.imgur.com/4AyK7BL.png)) –  Feb 07 '19 at 12:54
  • I have also tried other fields (so I'm not dividing status by status), but it also results in the same output. –  Feb 07 '19 at 13:08
  • No. duplicate exactly the Sum Case When {status} like 'Closed%' then 1 else 0 End but select a function '% of Total' in the function column. see edited answer – bknights Feb 07 '19 at 17:29