Lists are groups of related Items, such as people in a department, products, or the regions comprising a geographic area. They are fundamental to Pigment as they define the structure and content of a model.
🎓 Pigment Academy
Check out this three-minute video on the difference between Dimensions and Transaction Lists in the Academy course Pigment Lists.
List types
Dimension Lists
Dimensions define the structure of your data. These Lists are created once and then used throughout your entire Application. They can also be shared to other Applications. In most Applications, you have Calendar Dimensions, such as Years, Quarters, and Months. There are also Application-specific examples such as Products, Countries, Departments, and Employees.
Think of Dimensions as a way to describe how you want to display your data. A good practice is to use the word “by” before them. For example, I want to display the list of Employees by Month, by Country and by Cost center in a table. In this case, create the Months, Countries and Cost Center dimensions in the application so that you can reuse them within other Metrics and Tables.
Lists are made of Items. Each Item in a Dimension List must be unique. Pigment needs to know which Item is being referenced when you are using the Dimension Lists in multiple Metrics.
Options such as Hide empty rows or columns or Dynamic Selection can be used to hide sparse cells.
Transaction Lists
Transactions Lists are lists of Items that need to be transformed or grouped for analysis. Unlike Dimension Lists, Transaction List Items do not need to be unique.
They usually represent a list of events. For example a list of orders, transactions or a human resources ledger that use functions and formulas such as the BY modifier to group for analysis.
Transactions Lists can not be used to structure a Metric. They can only be referenced in formulas.
Change the type of List
You can change the type of List from Dimension to Transaction and vice versa. If a Dimension List is used in the structure of a Metric, it must be removed from the structure before it can be converted into a Transaction List. If it is just referenced, it can be changed.
Use the following steps to change the List type:
Navigate to the List you wish to change
Click on Block settings (cog wheel) in the top right corner.
In the General section, under List type, click the “Change to” button.
Properties
List Properties contain metadata—additional information about List Items. For example, in a list of Employees, you could add Properties such as Employee Preferred Name, the Department they work in, etc.
Properties can be used in calculations or to drive the aggregation of data through a model. Group properties can be used to establish hierarchies.
Creating a new Property
To create a new Property, click on the Configure button, the configure panel opens and you see the sections Pages, Groups and Properties. Click on the + button in the Properties section. You can also right click on a heading in the List and select Insert new Property right or Insert new Property left. This allows you to easily choose the location of your Property. If you add within the Configure panel, you can always drag and drop Properties in the panel to reorder them.
In the dialog that opens:
Give it a Name
Select a Type (see below, "Property types" section)
Define if the Items of this new Property should be Unique or not. This constraint ensures that all values are different from one another preventing users to enter the same value twice.
Click Save, if you want to add another Property, click Save & add another.
Filling a Property with a formula
Converting formula to values
List Properties can be defined either with manual inputs (values) or with a formula.
For columns defined by formulas, you can convert to values:
After selecting any cell of the Property, select the three dots menu to the right of the formula bar.
Select Convert formula to values.
Select Convert in the precautionary dialog.
⚠️ Important
Members with the Configure Blocks permission always see the Convert formula to values button.
However, if Access Rights inheritance is enabled in the Application, the button is disabled for any Members without the Can Define Security permission.
Editing and deleting Properties
To modify or delete a Property:
Open the Configure panel.
Go to the Properties section.
Select the three dots for Property settings in the Property you want to edit.
Select Edit Property.
From there, you can change the Property’s name or type or delete this Property.
🛡️Warning
Note that if you change the type of a Property with data in it, the data is deleted. If you add a unicity constraint, duplicated inputs are removed.
List's Items
Items belong to a List. They can be referenced anywhere in the model by using a formula with syntax '<List Name>'."<List Item>"
🛡️ Warning
Note that if you change an Item of a List, all the Metrics associated with it are immediately updated.
How to add an Item
From within the List, click on + Add item to create a new Item. If you want to create multiple Items, you can select the Add multiple Items icon and choose between adding 5, 10, or a custom amount of Items.
How to reorder Items
Click on Edit List.
Use the small arrow to order your Items.
Once finished, click on the button Done.
How to Delete Items
Click on Edit List.
Tick the box of the Items you want to delete.
Click on the button Delete to remove the Items.
Once finished, click on the button Done.