Why can't I sort on negative amounts?

Whether or not you're able to sort on a negative value in Clari is dependent upon a limitation to the conditions in a query that Salesforce will accept when you're using multicurrency in Salesforce. When sorting on a currency field, Clari looks for the next set of opportunities by asking Salesforce for the next group of deals with a value less than or greater than the value of the last retrieved opportunity. If an opportunity with a negative value in the field that you're sorting is the last retrieved opportunity, then the Salesforce query fails because Salesforce will not accept a query such as currencyField < ISOCode-VALUE (i.e. opportunity.amount < USD-100).

Dec 19, 2023
Knowledge

Created Date

10/11/2026, 7:31 AM

Last Published Date

12/19/2023, 9:45 PM

Article

Why can't I sort on negative amounts?

Overview

Whether or not you're able to sort on a negative value in Clari is dependent upon a limitation to the conditions in a query that Salesforce will accept when you're using multicurrency in Salesforce.

When sorting on a currency field, Clari looks for the next set of opportunities by asking Salesforce for the next group of deals with a value less than or greater than the value of the last retrieved opportunity. If an opportunity with a negative value in the field that you're sorting is the last retrieved opportunity, then the Salesforce query fails because Salesforce will not accept a query such as currencyField < ISOCode-VALUE (i.e. opportunity.amount < USD-100).

What Can I Sort By?

You'll be able to sort on an amount field and view the query results in Clari unless and until a negative value is retrieved in the query that's running via Salesforce.

So, if you have 1,000 opportunities in your Saved View results, and only the last few have negative values, then the Saved View will function as expected unless and until you scroll all the way to the last few opportunities with negative values at the bottom of the list.

Opening a Support Ticket

If you are being affected by this and want to open a ticket with Salesforce, you can use the information below:

Issue with SOQL API calls "where" multi currency field greater than ">"" or less than "<"" a negative value:

1. API SQL get where opportunity amount = positive value "USD9877.4":
/services/data/v39.0/query/?q=Select Amount From Opportunity WHERE Amount = USD9877.4

returns payload:

{
"totalSize": 1,
"done": true,
"records": [
{
"attributes": {
"type": "Opportunity",
"url": "/services/data/v39.0/sobjects/Opportunity/###################"
},
"Amount": 9877.4
}

2. API SQL get where opportunity amount < postive value "USD9877.4":
/services/data/v39.0/query/?q=Select Amount From Opportunity WHERE Amount < USD9877.4

returns payload:

{
"totalSize": 1,
"done": true,
"records": [
{
"attributes": {
"type": "Opportunity",
"url": "/services/data/v39.0/sobjects/Opportunity/###################"
},
"Amount": 0.0
}

3. API SQL get where opportunity amount < negative value “USD-9877.4":
/services/data/v39.0/query/?q=Select Amount From Opportunity WHERE Amount < USD-9877.4

returns error in payload:

[
{
"message": "From Opportunity WHERE Amount < USD-9877.4\n"
"^\nERROR at Row:1:Column:46\nBind variables only allowed in Apex code",
"errorCode": "MALFORMED_QUERY"
}
]

Workarounds

Some customers decide to change the amount fields that they are referencing in order to avoid the negative value issue.

This can be achieved by either picking an amount field that tracks values in positive numbers (tracking negative churn using positive numbers), or by using a field that only tracks increases (to avoid the negative value issue completely).