MAX() Function in Salesforce | Calculate maximum value of field in Salesforce

In this Salesforce Tutorial, we will learn the MAX() Function in Salesforce and how to calculate the maximum value of the field in Salesforce. In addition, we will learn the syntax and some practical business use cases of the MAX() Function.

I recently joined a USA-based IT company that uses the powerful customer relationship management system “Salesforce” to deal with the business world. One day I got a task to calculate the maximum lead score among all the leads.

As a solution, I discovered that Salesforce gives us the function named “Max() Function” which is used to calculate the maximum value of the field.

So, let’s move and learn how to use this MAX() function to calculate the maximum value of the field in Salesforce Lightning and in Salesforce Classic.

What is MAX() Function in Salesforce

The MAX() Function is one among numerous mathematical aggregate functions in Salesforce that may be used to carry out mathematical computations. This MAX() function allows us to find out the maximum value or you can say the highest number from the list of numbers in Salesforce.

The syntax of the MAX() Function is as given below:

MAX(number/field/formula)

The parameter value may take the number, field, or advanced formula as the value of the MAX() function.

Furthermore, the MAX() function only supports numeric data types like Numbers, Decimals, and Currency. And if we try to use text, dates, or other non-numeric data types, it will produce an error.

Now, let’s take an example that helps us to understand the concept and syntax of the MAX() function.

Suppose, we want to find out the maximum number of users associated with an account. For this, I use the MAX() function in Salesforce, and here is the formula for this:

MAX(NumberOfUsers__c)

With this, we learned the syntax, explanation, and example of the MAX() function. Let’s go on and see how to use Salesforce Lightning to determine the highest number from a list of numbers.

Calculate maximum value of field in Salesforce Lightning

Here, are the steps to find out the maximum value of the field in Salesforce Lightning:

Step 1: Log in to Salesforce Lightning mode, then click the “Gear Icon” in the top right corner of the page. Click “Setup” from the dropdown menu to continue.

MAX Function in Salesforce

Step 2: From the left side of the menu, click on “Object Manager”.

MAX Function in Salesforce Example

Step 3: Search for the object in the search bar where we wish to utilize the MAX() function. In this case, I search for the “Contact” object as I want to track the maximum age of the client based on the date of birth.

Step 4: Now, click on the “Contact” object and it will redirect us to the contact page.

MAX Function in Salesforce Lightning

Step 5: From the left side of the page click on the “Field & Relationship” option.

Step 6: Click on the “New” button to create a new field where we can use the MAX() function.

MAX Function in Salesforce Lightning Example

Step 7: It will redirect to the “Choose the field type” page, now check the radio button in front of the “Formula” data type.

Step 8: To proceed to the next step, click on the “Next” button.

Calculate maximum value of field in Salesforce

Step 9: Enter the “Field Label” for the formula and the unique “Field Name” will be automatically populated. In this case, I entered the field label named “Maximum Age”.

Step 10: Choose the “Data Type” for the field for which we want to calculate the maximum age of the client.

Here, I select the data type as “Number” to get the formula return type as a number, so I check the radio button in front of it and I also set the “Decimal Place” to 0.

Step 11: Move to the next step by clicking on the “Next” button.

Calculate maximum value of field in Salesforce Example

Step 12: Move to the advanced formula editor, and enter the formula that uses the MAX() function. In this case, I want to calculate the maximum age of the client using the MAX() function. Here, is the formula:

MAX(YEAR(TODAY()) - YEAR(Birthdate))
  • Here, we subtract the birth date year YEAR(Birthdate) from the current year YEAR(TODAY()).
  • Then, we pass the resultant value to MAX() function to calculate the maximum age of the client.

Step 13: Click on the “Check Syntax” button to validate the formula and ensure that there are no errors.

Step 14: Optionally, we can “Handle the empty field” as well as we can define the “Description” and “Help text” if we want.

In this case, I define the Description as “Find Maximum Age of the Contact” and the Help Text as “Use MAX Function”.

Step 15: Click on the “Next” button to move to the formula field-level security step.

Calculate maximum value of field in Salesforce Lightning

Step 16: Select the relevant “Field Level Security”. I have selected “Visible” in this case because I want it to be visible to everyone. Then, click on the “Next” button.

Calculate maximum value of field in Salesforce Lightning Example

Step 17: Now, select the appropriate “Page Layout” where we want to be visible.

Step 18: Click on the “Save” button to save the formula field.

How to Calculate maximum value of field in Salesforce Lightning

We can use the formula field once we’ve created it. Let’s look at an example:

  • Open the Contact Tab and create a new contact with the field “Birth Date”.
  • After that, click on the Details section, and here the “Maximum Age” field appears which shows you the result of the MAX() function.
How to Calculate maximum value of field in Salesforce Lightning Example

As a result, we now know how to calculate the maximum value of a field in Salesforce Lightning. Now, we’ll look at how to calculate the maximum value of a field in Salesforce Classic.

Calculate maximum value of field in Salesforce Classic

Here, are the steps to find out the maximum value of the field in Salesforce Classic:

Step 1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and then click Switch to Salesforce Classic.

MAX Function in Salesforce Classic

Step 2: After that, click on the Setup option.

MAX Function in Salesforce Classic Example

Step 3: Now, scroll down to the Build section. Then under Customize, select the object for whom field we want to use the MAX() function. In this case, I select the “Products” object as I want to track the maximum price of the product.

Step 4: Click on the “Products” dropdown and select the “Fields” option to get the option of the new field.

Calculate maximum value of field in Salesforce Classic

Step 5: Now scroll down to the “Product Custom Fields & Relationships” and click on the “New” button to create a new formula field.

Calculate maximum value of field in Salesforce Classic Example

Step 6: It will redirect to the “Choose the field type” page, now check the radio button in front of the “Formula” data type.

Step 7: To proceed to the next step, click on the “Next” button.

How to Calculate maximum value of field in Salesforce Classic

Step 8: Enter the “Field Label” for the formula, and the distinct “Field Name” will be immediately filled out. In this example, I entered “Maximum Price” as a field label.

Step 9: Choose the “Data Type” for the field for which the maximum value is to be determined. As I want to get the maximum price, I selected the “Currency” as the formula return type by checking the radio button next to it.

Step 10: Click the “Next” button to move to the next step.

How to Calculate maximum value of field in Salesforce Classic Example

Step 11: Now, it will navigate to the advanced formula editor. Type the formula that computes the maximum value from the list using the MAX() function.

In this instance, I want to use the MAX() function to determine the maximum price of the product. The formula is as follows:

MAX(100, 1023, 560, 894)

Here, we pass the list of the numbers to the MAX() function, and the max function picks up the highest number from the list of numbers and set it as the “Maximum Price” of the product.

Step 12: Tap “Check Syntax” to review the formula and ensure there are no errors.

Step 13: Define the “Description” and “Help text” as well as “Handle the empty field” if you would like.

Step 14: Click “Next” to proceed to the following step.

Calculate maximum value of field using Salesforce Classic

Step 15: Choose the appropriate “Level Security” for the profiles. As I want it to be visible to every profile, I have checked the checkbox next to “Visible” in this instance.

Step 16: Click on the “Next” button to move to the final step.

Calculate maximum value of field using Salesforce Classic Example

Step 17: Next, choose the suitable “Page Layout” for the field where we want to display this field.

Step 18: In order to save the formula field, click the “Save” button at the end.

How to Calculate maximum value of field using Salesforce Classic

We can use the formula field once we have created it. Let’s look at an example and understand how its works.

  • Open the “Products” item and create a new product and then save it.
  • After saving, it will redirect to the Details Page, and here the “Maximum Price” field appears which shows the result of the MAX() function.
How to Calculate maximum value of field using Salesforce Classic Example

As a result, we now understand how to use Salesforce Classic’s MAX function to determine the maximum value of the list.

Conclusion

With this, we have concluded that the MAX() Function in Salesforce is a great and efficient way to find out the maximum value from the list. In addition, we focused on the syntax and various real-world business-related examples of the MAX() Function.

Furthermore, we covered the process of calculating maximum value using the MAX() Function in Salesforce Lightning and in Salesforce Classic step by step.

You may like to read: