Search that displays the Total Projected Amount of each Opportunity Status grouped by Sales Rep with Percent Change for today and same day of last fiscal year

To create the search, perform the following steps:

1. Navigate to Lists > Search > Saved Searches > New.

2. Select Transaction.

3. Provide a Search Title.

4. On the Criteria tab > Standard subtab, set the following:

 

Type is Opportunity

Main Line is true

5. On the Results tab > Columns subtab, set the following:

 

Field	Summary Type	Formula	Custom Label
Sales Rep	Group		Sales Rep
Formula (Currency)	Sum	CASE WHEN (TO_CHAR {entitystatus} in ('xxx')) AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (CURRENT_DATE,'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END	$ 'xxx' (Today)
Formula (Currency)	Sum	CASE WHEN (TO_CHAR {entitystatus} in ('xxx'))  AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (add_months (CURRENT_DATE, -12),'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END	$ 'xxx' (Same Day of Last Year)
Formula (Percent)	Sum	CASE WHEN ( CASE WHEN (TO_CHAR {entitystatus} in ('xxx'))  AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (add_months (CURRENT_DATE, -12),'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END ) = 0 THEN (CASE WHEN (TO_CHAR {entitystatus} in ('xxx')) AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (CURRENT_DATE,'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END) ELSE(CASE WHEN (TO_CHAR {entitystatus} in ('xxx')) AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (CURRENT_DATE,'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END - CASE WHEN (TO_CHAR {entitystatus} in ('xxx'))  AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (add_months (CURRENT_DATE, -12),'MM/DD/YYYY') THEN {projectedamount} END)/ CASE WHEN (TO_CHAR {entitystatus} in ('xxx'))  AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (add_months (CURRENT_DATE, -12),'MM/DD/YYYY') THEN {projectedamount} END END	% Change

 

Note: Replace all 'xxx' with the Status, for example, for 'Closed Won' status, formula should look like:

 

CASE WHEN (TO_CHAR {entitystatus} in ('Closed Won')) AND TO_CHAR ({datecreated},'MM/DD/YYYY') = TO_CHAR (CURRENT_DATE,'MM/DD/YYYY') THEN {projectedamount} ELSE 0 END

 

6. Click Save & Run