Pigment formula syntax has a lot of similarities with Excel. Basic operations are done in the same way and a lot of functions use the exact same parameters and syntax.
Table of contents
You must have the Configure Blocks permission to write formulas. The formula bar will appear grayed out for those who do not have this permission.
Key things to keep in mind
-
Pigment objects (Lists, Properties, Metrics) are named, so you will directly reference their names in formulas
(Revenue * Growth)and not cells like in Excel(B2 * D42). -
What would require tons of
VLOOKUP()in Excel can be done in seconds by just referencing any Property of a Dimension used in a Metric ( do you need the country currency in your 'Revenue' Metric defined by country? Just typecountry.currency). -
Since Pigment is multi-dimensional, you'll need to learn a few tricks to aggregate and allocate data. Check out the Aggregating data in formulas article.
Formula Structure and Syntax
-
Periods separate list properties (Dimension and Transaction) and relationships between dimensions country.currency and chain properties together
-
Commas separate arguments
Referencing Objects
Unlike Excel, the formulas you enter in Pigment apply directly to all the cells of an object. An object can be a Metric or List Property. To perform operations between those objects, Pigment just needs to know their name. The syntax is simple:
-
to reference a Metric, just write its name between single quotes:
'Metric'
Example:'Revenue', 'Margin' , 'Staff Cost', etc.
-
to reference a List Property, write the List name, dot, the Property name
'List'.'Property'
Example: 'Employee'.'Salary' , 'Product'.'Category', etc.
Caveat
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
Using Periods to Reference Relationships between List Properties
In a Metric, you can reference Properties of any Dimension it is defined on. The syntax to reference a Property, as describe above, is List.Property.
For example, in a Metric defined by City and Month, you can lookup any Property of Country or Month:
| Case | Return |
|
| returns the country of each city |
|
| returns the number of inhabitants of each city |
|
| returns the quarter of each month |
|
| returns the year of each month |
You can also reference Properties of Properties by "chaining" them in the formula:
|
| returns the Region of every City |
|
| returns the Currency of every Order |
Using Parenthesis versus Square Brackets
-
Parenthesis enclose arguments
-
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
|
| Set 1 for all the cities in France, 0 for the others |
|
| Sums the Property Amount of the Orders list by the Property Country |
|
| Lookup the VAT Property of each Orders' Country in the Country List |
Referencing 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 the 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
For example, if you want to reference a specific month, in Pigment you write 'Month of Year'.Name."December" 'Month of Year'.Name."December"
Below are some more 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 the page on the FILTER keyword) |
Excluding 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. The following works for numeric and item values. In these instances you can use <> and !=
Revenue[FILTER: Revenue <> 0] | In a Metric Revenue, brings in revenue data that is not equal to 0 |
Revenue[FILTER: Revenue !=0] | != is the same as <>, it can also mean not equal to. In a Metric Revenue, brings in revenue data that is not equal to 0 |
IF(City.Country <> Country."France", 1, 0) | If the Cities country is not equal to France then 1 else 0. This will set the value to 1 for all cities that are not in France. |
Performing basic operations
Operations on numbers
Basic operations between numerical objects and numbers can easily be made using those 4 operators: additions +, subtractions -, multiplications *, divisions /. Here are some examples:
|
| Number 100 will be filled in every cell |
|
| 1 will be added to all the cells |
|
| Quantity will be multiplied by Seasonality |
|
| Quantity will be multiplied by Unit Price |
|
| Cost of Sales will be subtracted from Revenue |
Operations on texts
Concatenation of multiple Properties and/or strings can be done using the & operator:
|
| The string between double quotes will be filled in every cells |
|
| 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:
|
| Returns 1st of January 2021 in every cell |
|
| Returns 31st of January 2021 in every cell |
|
| Returns a calculation based on the value set in the Metric (If Empty or 0, returns the same date. If positive, returns a date posterior. If negative, returns a date anterior) |
|
| Returns the number of days between the last day of the current month and the day the employee joined |
🎓 To learn more about formula writing in Pigment, explore the Introduction to Formulas and Basics of Formula Writing modules in our Academy.