Tuesday, April 30, 2019

Saved search which computes rounded "% of Total" function

Users would like to create a Case saved search where the % of Total function and the Round function are used at the same time. 

See the example of saved search which computes all Closed cases within the fiscal year divided into quarters against all cases in the fiscal year:

1. Navigate to the Lists > Search > Saved Searches > New > select type as Case.
2. On the Criteria subtab > Standard sublist set following:
Status = is Closed
Date Created = is within this fiscal year
3. On the Results subtab set following:

Divide cases into quarters:
Field = Formula (Date)
Function = Quarter
Formula = {createddate}
Customer Label = Quarter

Set the grouping of cases:
Field = Type
Summary Type = Group

Closed Cases in Q1 (numeric):
Field = Formula (Numeric)
Summary Type = Sum
Formula = DECODE(to_char({createddate},'Q'),1,1,0)
Summary Label = Q1

Rounded percentage of Closed cases in Q1:
Fields = Formula(Percent)
Summary Type = Maximum
Function = Round or Round to tenths (according to your requirements)
Formula = ratio_to_report(sum((DECODE(to_char({created},'Q'),1, 1, 0)))) over ()
Summary Label = Q1 %

Total percentage of closed cases within the fiscal year by Type of case:
Fields = Formula(Percent)
Summary Type = Maximum
Function = Round or Round to tenths (according to your requirements)
Formula = ratio_to_report(COUNT(DISTINCT{internalid})) over ()   
Summary Label = Total %

See the picture below for all settings of the Results subtab:

No comments:

Post a Comment