5 Replies Latest reply on Sep 13, 2016 5:57 AM by Yuriy Fal

# Max Close Date for Certain Sold Products

I'm looking to create a calculated field that returns the last date of sale for an specific product for each of my Accounts

Ideally something like this would work :

IF Stage="Closed" AND Product="Software" THEN MAX(Close Date) ELSE "NA"

The result would be something like this table:

Account NameLast Software Sale

Account 1

12/3/2015
Account 22/1/2014
Account 3NA
Account 410/15/2014

My data set includes Salesforce Accounts with Opportunities(with Products)

Is there any trick I could use to get around the IF statement?

Thanks

• ###### 1. Re: Max Close Date for Certain Sold Products

Is the problem that you can't mix strings ('NA') and the max date? Is this why you are trying to avoid the IF? If so, just convert the date to a string.

• ###### 2. Re: Max Close Date for Certain Sold Products

I already tried converting the date field to string but that didn't solve the issue. I only get a valid formula if I do the following:

IF ATTR([Status])="Closed" AND ATTR([Product])="Software"

THEN MAX([Close Date ]) ELSE "NA"

END

However this only returns a date for accounts with opportunities that have one "Software" line item any other accounts returns "NA". One account can have multiple opportunities and each opportunity contains multiple product rows.

• ###### 3. Re: Max Close Date for Certain Sold Products

Try

IF ATTR([Status])="Closed" AND ATTR([Product])="Software"

THEN STR(MAX([Close Date ])) ELSE "NA"

END

• ###### 4. Re: Max Close Date for Certain Sold Products

Thanks for the quick feedback. I tried that calculation but again I only get a result for accounts with one product, all other accounts returns "NA"

• ###### 5. Re: Max Close Date for Certain Sold Products

Hi Fernando,

You may try this one:

IFNULL( STR( MAX(
IF [Stage] = "Closed" AND [Product] = "Software" THEN [Close Date] END
) ), "NA")

Hope it could help.

Yours,

Yuri