With simple formulas and arithmetic operators, =, -, <, >, you can compare Metrics, Dimensions and Transaction Lists, to validate data accuracy, set up Boolean filters and more.

Ordering logic used for comparisons

In a typical comparison exercise, Pigment determines whether the second of a pair of data points is greater than, lesser than or equal to the first. The comparison is based on a defined order, often alphanumeric, and returns a Boolean output of TRUE or FALSE values.

The order used for this comparison depends on the data type, as set out in the below table:

This logic is used for all comparisons apart from non-Calendar Dimension List comparisons. For non-Calendar Dimension List comparisons, Pigment uses the order in which the Items appear in the List. Members can manually change the order of these Items, as follows:

1. In your Application, select the Dimension you want to reorder from the left sidebar.
2. Select Manage Items from the top toolbar.
3. A column entitled Reorder appears to the left of your data. Use the arrows in that column to change Item order.

This article provides examples of the different types of comparison possible in Pigment.

Compare Dimensions

Comparison of Dimension-type expressions

A company wants to use Booleans to switch on Items in the bottom half of an income statement. It creates a Metric that compares the Dimension with itself, using this formula: 

Accounts > Accounts."G&A"

Where:

• “Accounts” - is a Dimension List storing the line item titles of the Income Statement
• “G&A” - is the line item after which it wants TRUE Booleans  

Note that the output uses the order of the items in the Dimension List, not alphabetical order, even though the Dimension List has a text data type. This is due to the ordering rule explained above. The comparison thus is based on the order of items in the Dimension, not the data type.

Comparison of a Dimension Property to a Dimension Property—Calendar example

Imagine the company is rolling out a new product across five cities. It holds its launch dates in a Metric, with data type Dimension (Week), and containing a Dimension within the Metric of City:

The Properties automatically created for Calendar Dimensions such as Start Date are also created for Metrics of type Calendar Dimension. The company creates a Boolean Metric to forecast flow of stock, through a comparison of Week Start Date, using the formula below. Here, the comparison uses date order, as that is the data type of the Properties being compared:

Week.'Start Date' > 'Launch date by city by week'.'Start Date'

Comparison of a Dimension Property to a Dimension Property—non-Calendar example

Now imagine the company supports its launches with promotional activity across the five cities where it is present. To distribute its promotional spending efficiently, it prioritizes its cities by market size. For this, its adds a Property called Priority to its Dimension City.

The company also decides on three kinds of promotional activity. It wants low-priority cities to have just leaflet advertising, medium-priority cities to have leaflets and billboards, and high-priority cities to have leaflets, billboards and TV advertising. It creates a Dimension defining tiers of cities for its different activities: 

To apply these tiers to its cities, it compares Dimension Properties. The formula is:

City.Priority < 'Promo activity tiers'.'Priority threshold'

Here, the comparison is based on numerical order as the Properties being compared have the integer data type. If a Dimension-type Property were being compared, it would use the Dimension item ordering instead.

Comparison of a Dimension to a Metric of type Dimension

A Dimension can be compared to a Metric of type Dimension. Following the example above, the below Boolean Metric “NPD rollout” forecasts the flow of stock by month and by city with this formula:

Month > 'Launch date by city'

Where:

• “Month” - is the native Calendar Dimension
• “Launch date by city” - is a Metric with data type Dimension (Month), and a Dimension within the Metric of City

Here, the comparison is between a Calendar Dimension and a Metric of type Dimension. Under the ordering rule explained above it compares based on date order.

Compare Metrics

Metrics use Dimensions as axes to break out data, as explained in Understand Pigment Blocks. When comparing Metrics with different Dimensions, remember the following key factors:

1. Dimensions have to be aligned carefully. See Mapping Source to Target Dimensions for guidance on aligning Dimensions.
2. Blanks in the data are handled differently according to the operator used. See How Pigment Handles Blanks for more information.

Below is a simple case, in which the Metrics compared have the same Dimensions. You wish to check whether two Metrics contain identical data. You opt to use =, <, >, and -:

1. Select the Formula Playground at the bottom of the sidebar.
2. Set up a formula in the form: 'Metric 1' = 'Metric 2'
3. The output is a Boolean. Checkboxes with no check marks indicate differences between the data sets.
4. Replace the = with < or > to see which Items in the second Metric are higher or lower than the first, based on the above ordering logic of contexts aside from Dimensions. 
5. You can use - to give absolute numerical values for these unders and overs. The Formula Playground automatically changes the data type to Number for you.
6. For large datasets, you can use an IF() formula such as the below, which returns blank for cells with no differences. These can be hidden using Hide empty rows and columns: 
   IF('Metric 1' = 'Metric 2', blank, 'Metric 1' - 'Metric 2')
   If your Metrics are equal, Pigment returns the message No data to display.

Compare Transactions List Properties

The arithmetic operators documented above in Compare Metrics can also be used for Properties of Transaction Lists.

For example, a company has a Transaction List from its payroll team of employee salary payments. It wants to set up a cross-reference of employee salaries as declared to the tax authority, held in a second Transaction List. 

It uses the following formula:

IF('Transactions A'.Amount pby sum: 'Transactions A'.'Dim A'] = 'Transactions B'.Amount lby sum: 'Transactions B'.'Dim A'], blank, 'Transactions A'.Amount lby sum: 'Transactions A'.'Dim A'])

Where:

• “Transactions A” and “Transactions B” are the two Transaction Lists

• “Amount” refers to the salary payment entries

• “Dim A” is the list of employee names

Here, the comparison is between Transaction List Properties, and so the ordering used is determined by the Properties’ data type. As it is the Number Data Type, numerical order is used.

The IF function isolates the cells with differences.

Learn more

• Set up your Calendar (Properties section)
• Write your first formula
• IF function
• FAQs: List Management