Writing your first formula

  • 21 December 2021
  • 0 replies
  • 1245 views

Userlevel 4
Badge +2
  • Community Manager
  • 10 replies

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.

However, because Pigment is more structured than a spreadsheet and multi-dimensional, it also has specificities you will need to learn. But don't worry, it's not that complicated!

 

 

Table of contents

 

 

Key things to keep in mind

 

  1. 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).

  2. 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 type country.currency).

  3. Since Pigment is multi-dimensional, you'll need to learn a few tricks to aggregate and allocate data (see Aggregating data in formulas).

 

 

Referencing Objects

 

Unlike Excel, the formulas you enter in Pigment apply directly to all the cells of an object (Metric or List Property). There is no drag and drop or copy/paste needed. This gives you the guarantee that the calculation is consistent and done without exception.

To perform operations between those objects, Pigment just requires to know their name. A bit like if you where working with Named Ranges in Excel. 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 optional when there is no space nor leading non-alphanumeric characters in the Metric name:

  • 'Quantity' can be written Quantity

  • '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 written Margin% because there is no space and it leads with a letter

  • 'Margin %' needs single quotes because there is a space

 

 

Using List Properties in Metrics

 

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

City.Country

returns the country of each city

City.Inhabitants

returns the number of inhabitants of each city

Month.Quarter

returns the quarter of each month

Month.Year

returns the year of each month

 

You can also reference Properties of Properties by "chaining" them in the formula:

 

City.Country.Region

returns the Region of every City

Orders.Country.Currency

returns the Currency of every Order

 

To reference a given modality of a Dimension List, append a dot and the Property name between double quotes after the list:

List."Property name"Example: Country."France", Employee."John Doe"

Using the two features we just described, formulas can be written like this:

 

IF(City.Country = Country."France", 1, 0)

set 1 for all the cities in France, 0 for the others

Orders.Amount[BY SUM: Orders.Country]

sums the Property Amount of the Orders list by the Property Country

Country.VAT[BY CONSTANT: Orders.Country]

lookup the VAT Property of each Orders' Country in the Country List

 

 

Targeting a specific List 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" but we made the Property optional to make it easier to point to an item. If omitted, Pigment will use the Default Property (see the table below for more information)

 

Country."France"

Points to France in the Country List

Employee."Ben"

Points to Ben in the Employee List

Month."Jan 21"

Points to Jan 21 in the Month List

Revenue[FILTER: Month = Month."Jan 21"]

In a Metric Revenue defined by Month, keeps only the data of Jan 21 (see the page on the FILTER keyword)

 

Setting an Inequality Operator 

 

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:

 

100

number 100 will be filled in every cell

Quantity + 1

1 will be added to all the cells

Quantity * Seasonality

Quantity will be multiplied by Seasonality

Quantity * 'Unit Price'

Quantity will be multiplied by Unit Price

Revenue - 'Cost of Sales'

Cost of Sales will be subtracted from Revenue

 

Operations on texts

Concatenation of multiple Properties and/or strings can be done using the & operator:

 

"Text displayed everywhere"

the string between double quotes will be filled in every cells

Employee.'First Name' & " " & Employee.'Last Name'

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:

 

DATE(2021,1,1)

returns 1st of January 2021 in every cell

DATE(2021,1,1) + 30

returns 31st of January 2021 in every cell

DATE(2021,1,1) + 'Offset Metric'

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)

Month.'End Date' - Employee.'Start Date'

returns the number of days between the last day of the current month and the day the employee joined

 

 


This topic has been closed for comments