CODEWITHSUNDEEP
sqlnetsuite
AFleetwood
AFleetwood

Reputation: 31

NetSuite Saved Search formula for Opportunities closed within a certain time frame

I want to create a Saved Search that displays all Opportunities that were closed in 2018. How do I write a formula that filters on

Status = Closed Won & Set by Date = 2018?

I'm trying to determine actual date status was changed to closed and filter to those only closed in 2018

{systemnotes.date} >= '01/01/2018' AND
{systemnotes.date} <='12/31/2018

Need a saved search for top reps of Closed-Won Opps for 2018 for awards.

Upvotes: 1

Views: 751

Answers (1)

Mike Robbins
Mike Robbins

Reputation: 3287

There is an Actual Close field on the Opportunity record that holds the date the opportunity status was changed to either Closed Won or Closed Lost.

You should be able to add the following criteria without a formula:

Opportunity Status = Closed Won
Actual Close within last year (2018)

If you use the system notes to filter the actual time the status was changed to Closed Won, you risk returning multiple records for each opportunity if the status was changed to Closed Won, then back to an earlier status, then back to Closed Won again. It could be done like this, again without needing a formula.

Opportunity Status = Closed Won
System Notes : New Value starts with Closed Won
System Notes : Date within last year (2018)

If you must use a formula to find these records you can use

case 
    when {entitystatus} = 'Closed Won' 
         and {systemnotes.newvalue} = 'Closed Won' 
         and {systemnotes.date} >= '1/1/2018' 
         and {systemnotes.date} <= '12/31/2018' then 1 
    else 0 
end

equal to 1

Upvotes: 2

Related Questions