The COMPARE function compares text values. If they're the same, it returns 0. If the first text value is greater, it returns 1, and if the first text value is less, it returns -1.

You could use COMPARE as the basis of conditional formatting for identical text values.

COMPARE(Text to compare 1, Text to compare 2 [, Comparison mode] [, Locale])

ArgumentData typeDescription
Text to compare 1 (required)TextThe first text value to compare.
Text to compare 2 (required)TextThe second text value to compare.
Comparison modeKeyword

The comparison mode to use.

The available keywords are IDENTICAL, PRIMARY, SECONDARY, and TERTIARY.

There's more information in the Comparison mode argument keywords section below.

LocaleKeyword

The locale to use. This only affects certain languages.

The available locales are based on ISO 639 and ISO 3166 codes.

The COMPARE function returns a number.

For more information about how to determine if 

KeywordDescription
IDENTICALCompares the Text to compare 1 and Text to compare 2 arguments to check if they're identical in all regards.
PRIMARY

Compares the base characters of the Text to compare 1 and Text to compare 2 arguments.

The alphabetical order of text values is compared from left to right, in line with the locale chosen.

If the starting characters of two text values are the same, but one contains more characters, the string with more characters is greater.

SECONDARY

Compares the base characters and accents of the Text to compare 1 and Text to compare 2 arguments.

If the base characters of two text values are the same, COMPARE considers the one that contains accents as greater.

TERTIARY

The default keyword if you omit the Comparison mode argument.

Compares the base characters, accents, and case of the Text to compare 1 and Text to compare 2 arguments.

If the base characters and accents of two text values are the same, but one contains uppercase characters, COMPARE considers the one that contains uppercase characters as greater.

COMPARE(Top Salesperson North, Top Salesperson South)

Whether a text value is considered greater than or less than another text value is based on several criteria, similar to collation in Unicode or Java.

The Comparison mode keywords compare the base characters, accents, and case of text values respectively. However, this behavior varies depending upon the locale chosen.

When you use the SECONDARY or TERTIARY keywords to compare the accents or case of text values, the COMPARE function also applies prior criteria. For example, if you use TERTIARY to compare the case of text values, it first compares the base characters and accents.

The Locale argument accepts ISO language codes that correspond to the locales available in Java 8. You can use either:

  • Two letter ISO 639 language codes such as en, fr, de, or tr.
  • Four letter codes made of both ISO 639 language codes and ISO 3166 country codes such as en-us, fr-be, de-at, or tr-tr. However you must replace the hyphen (-) with an underscore (_) in Anaplan, so these become en_us, fr_be, de_at, and tr_tr respectively.

The Locale argument only has an effect on certain languages. For example, Turkish, which is represented by the tr code. In Turkish, there are four versions of the character i. Lowercase and uppercase versions, both with and without a dot.

The Locale argument of the COMPARE function may alter the fundamental properties of a character, such as its position in the roman alphabet. Here are some examples of how the Locale argument can affect the COMPARE function:

  • With the English en language code, the Danish character ø is treated as if it's the base character of o. With the Danish da language code, the base character of ø is not considered to be an accented character, and it comes after z in alphabetical order.
  • In Swedish, represented by the sv language code, the characters v and w are considered to be the same.
  • In Lithuanian, represented by the lt language code, the character y precedes j in alphabetical order.

EXACT

In this example, Time displays on columns. On rows, there are three line items. The first two line items show the top sales performer for All Regions and North. The third line item contains a formula that uses COMPARE to see if the top sales performer in the North region is the same as in all regions.


Jan 21Feb 21Mar 21
Top Sales All RegionsPeterRashidJenny
Top Sales NorthPeterPeterJohn

Top Sales in North?

COMPARE(Top Sales All Regions, Top Sales North)

01-1

The formula uses the default behavior for the Locale argument, which is the ‘en’ ISO 369 language code.

In Jan 21Peter was the text value for both All Regions and North. As they're identical, COMPARE returns a value of 0.

In Feb 21Rashid was the text value for All Regions, and Peter the text value for North. As the first letter of both Rashid and Peter is different, the COMPARE function compares these characters. R comes after P in alphabetical order, so the text value Rashid is considered greater and the formula returns a value of 1.

In Mar 21Jenny was the text value for all regions, and John the text value for North. The first letter of both Jenny and John, J, is the same, so it's ignored in the comparison. This means the second letter of each text value is compared. As e comes before o in alphabetical order, the formula considers the text value John greater, and returns a value of -1.

If you use the PRIMARY keyword for the Comparison mode argument, the COMPARE function compares the two text values for any differences in base characters.


Jan 21Feb 21Mar 21
Chosen Employee 1TimTimTim
Chosen Employee 2TimTomTam
COMPARE(Chosen Employee 1, Chosen Employee 2, PRIMARY)0-11

If you use the SECONDARY keyword for the Comparison mode argument, the COMPARE function:

  1. Compares the two text values for any differences in base characters.
  2. If there's no difference in base characters, compares the accents in the two text values.

Jan 21Feb 21Mar 21
Chosen Employee 1SorenSorenSøren
Chosen Employee 2SorenSørenSoren
COMPARE(Chosen Employee 1, Chosen Employee 2, SECONDARY)0-11

If you use the TERTIARY keyword for the Comparison mode argument, the COMPARE function:

  1. Compares the two text values for any differences in base characters.
  2. If there's no difference in base characters, compares the accents in the two text values.
  3. If there's no difference in base characters or accents, compares the case of the two text values.

Jan 21Feb 21Mar 21
Chosen Employee 1FrankfrankFrank
Chosen Employee 2FrankFrankfrank
COMPARE(Chosen Employee 1, Chosen Employee 2, TERTIARY)0-11

This example shows how the result the COMPARE returns changes based on the Comparison mode argument.


Primary differenceSecondary differenceTertiary difference
Text 1báÁ
Text 2aaá
COMPARE('Text 1', 'Text 2', PRIMARY)100
COMPARE('Text 1', 'Text 2', SECONDARY)110
COMPARE('Text 1', 'Text 2', TERTIARY)111

When used for the Locale argument, some codes change how the COMPARE function treats characters. For example, if you use the Swedish language code, sv, the letters w and v are considered to be the same base character.


Region
EnglishSweden
SwedishSveden
COMPARE(English, Swedish, PRIMARY)1
COMPARE(English, Swedish, PRIMARY, sv)0