When you get started in Pigment, many operators and plenty of syntax will be familiar from Excel. However some syntax and basic operations are different. This guide helps you to kickstart your formula writing.
You must have the Configure Blocks permission to write formulas. The formula bar appears grayed out for those who do not have this permission.
Check out the following Pigment Academy courses before reading this article:
For more information on how Pigment compares to Excel, see the article Pigment Compared to Excel.
The basics
In Excel, you typically click in a cell where you’d like to add a formula, start with an “=” and then write your formula.
In Pigment, one formula applies to all data in the Block. At the top of the main pane, look for the lower case “f” by a blank rectangle—this is where to input your formula:
How to refer to Items in Pigment
While Excel calls on data using cell references, Pigment holds data in Blocks, including Metrics, Lists and Properties. You directly reference the Blocks’ names when writing formulas.
To reference a Metric, single quotes are required unless the Metric name starts with a letter and contains no space in the name of the Metric:
-
'Quantity'can be writtenQuantity -
'Quantity 42'needs single quotes because of the space -
'42'needs single quotes because it leads with a number -
'%Margin'needs single quotes because it leads with a non-alphanumeric characters -
'Margin%'can be writtenMargin%because there is no space and it leads with a letter -
'Margin %'needs single quotes because there is a space
To reference a List Property, write the List name, dot . , the Property name. For example, in a Metric containing lists of cities and the months, you can look up any Property of City or Month:
| Reference | Returns... |
|
| the country of each city |
|
| the number of inhabitants of each city |
|
| the quarter of each month |
|
| the year of each month |
After typing List names followed by dot, such as City. from the above example, Pigment provides all available Properties for that List in a dropdown, such as City.Country or City.Inhabitants, reducing keystrokes.
You can also reference Properties of Properties by "chaining" them in the formula:
| Reference | Returns... |
|
| the Region of every City |
|
| the Currency of every Order |
Parentheses versus square brackets
-
Parentheses enclose arguments, commas separate them
-
Square brackets introduce formula modifiers: BY, ADD, REMOVE, FILTER, SELECT and EXCLUDE
-
Reference the Functions and Modifiers app for the correct structure and syntax for all functions and modifiers
|
| Creates a Boolean of “1” against all cities that are in France, and “0” against all cities outside France |
|
| Sums the Property Amount of the Orders list by the Property Country |
|
| Looks up the VAT Property of each Orders' Country in the Country List |
Reference a specific Item
In some cases, you want to reference a specific Item of a List (a specific country, a specific month, a specific product category and so on).
The complete syntax here is 'List'.'Property'."Item". Notice how Item is in double quotes. The Property is actually optional to make it easier to point to an Item. If omitted, Pigment will use the Default Property.
Below are some examples.
|
| Points to France in the Country List |
|
| Points to Ben in the Employee List |
|
| Points to Jan 21 in the Month List |
|
| In a Metric Revenue defined by Month, keeps only the data of Jan 21 (see more about filtering here) |
After typing List names followed by dot and double quotes, such as Country." from the above example, Pigment provides all available specific items for that List in a dropdown, reducing keystrokes.
Exclude an Item or value
There will be instances when you want to set a condition to exclude an Item or value. For example, you might want to say that a value is not equal to 0. You can choose between using <> or !=. The following works for numeric and Item values.
Revenue FILTER: Revenue <> 0] | In a Metric Revenue, brings in revenue data that is not equal to 0 |
RevenuerEXCLUDE: Month."Feb 19"] | In a Metric Revenue, brings in month data apart from for February 2019 |
IF(City.Country <> Country."France", 1, 0) | Creates a Boolean of “1” against all cities that are outside France, and “0” against all cities in France |
Basic operations in Pigment
Operations on numbers
Basic operations between numerical objects and numbers can easily be made using those four operators: additions +, subtractions -, multiplications *, divisions /. Here are some examples:
|
| Number 100 will be filled in every cell |
|
| 1 will be added to all non-blank cells |
|
| Quantity will be multiplied by Seasonality |
|
| Quantity will be multiplied by Unit Price |
|
| Cost of Sales will be subtracted from Revenue |
Operations on text
Concatenation of multiple Properties and/or strings can be done using the & operator:
|
| The string between double quotes will be filled in every cell |
|
| Concatenate all employee's First and Last names adding a space in between |
Operations on dates
Adding or removing a certain number of days is also possible using + and -, exactly like in Excel:
| Reference | Returns... |
|
| 1st of January 2021 in every cell |
|
| 31st of January 2021 in every cell |
|
| a calculation based on the value set in the Metric. If empty or 0, returns the same date. If positive, returns a later date. If negative, returns an earlier date |
|
| the number of days between the last day of the current month and the day the employee joined |
Operations on Dimension Items
You can offset Dimension Items using integers. For example, you could set up a Time Dimension to show months alongside prior months to allow month-over-month comparisons—Month-1:
- This works on any Dimension, not just time Dimensions.
- The integer doesn't have to be hardcoded. It can be a Metric with the Integer data type.