Getting a wrong result in a formula or not being able to get a formula to work can be a frustrating experience. This article provides a few tips in two categories: not getting the expected value and not being able to get a formula to execute.
Troubleshooting formulas with the wrong result
The tips in this section are for when you’re getting a wrong result.
Identify where the problem is
Before getting too deep into troubleshooting a formula, follow these steps to see if the problem exists in the current formula.
Have a look at your filtering rules
Sometimes, the result is correct but does not appear. If you have sparse data, or data with a lot of blanks, sometimes the rows within the View do not have any values associated with them. Ensure the correct filtering rules are activated.
If no data is displayed, have a look at the Hide empty rows / cols option. It could just be the data is not in view. Turn on Hide empty rows / cols to remove any blank rows and bring more data into view.
Check the correctness of the referenced data sources
Sometimes a wrong result can be because the referenced data is incorrect. To ensure the data source is correct, use the dependency diagram to investigate. 🕵️
You can preview and access each Block from the Dependency Diagram to check that the data is correct in these Blocks.
Ensure you have the Access Rights
Access Rights architecture can be complex and can evolve. Ensure you have the necessary Access Rights to compose your formula and access the data. The formula could be correct but your Access Rights configuration is blocking you from viewing the data. Reach out to your Workspace Admin to have them check your Access Rights.
Multiple Dimensions in formulas
Pigment is multi-dimensional, meaning that your Metrics are structured on one or many Dimensions (like Country, Months, Cost Center). It’s important to understand that when you want to cross Metrics together, their Dimensions have to be aligned.
For example, you have an Orders Volume per Country Metric (with Country Dimension) and you want to multiply it by an Average Selling Price Metric (with Month dimension). You end up with a result, but probably not the one you expected. In this case, it would be better for both Metrics to have the same Country and Month Dimensions before multiplying them into a new Metric. Check out this article to understand more about understanding multiple Dimensions.
🎓 Pigment Academy
To learn more, visit our Academy course: Work with Dimensionality in Formulas.
Check the structure of the current Metric
Click on the settings of your Block and check the structure, verify the data type and the Dimensions within this Metric. Knowing the Dimension can help you identify how to structure your formula. The data type is important to make sure it matches the resulting data type of the functions within your formula. If the data type is incorrect, you can change it, but understand you lose any data in that Metric. Understanding the Metrics’ Dimension structure is important to ensure your formula’s results are aligned. Use the Formula Playground in Automatic mode to see the output of your formula.
Check the Dimensions of any referenced Metrics
Hover over each Metric in your formula to see its Dimensions. If the Dimensions of the source and target are not aligned, the value might not be as expected. For example, if you reference a Metric using the Month Dimension and your Metric does not have that, your values might be 12 times higher, depending on how many years you have set up. You might use a modifier to align the Dimensions. You can learn more about source to target mapping and when to use a Modifier in this article.
🎓 Pigment Academy
To learn about aligning Block Dimensions and Source to Target Mapping, visit our Academy courses: Align Block Dimensions for Formula Operations, Work with Dimensionality in Formulas and the https://academy.gopigment.com/source-to-target-mapping-tool.
️‍Use the Formula Playground feature to test
As its name suggests, you can try anything in the Formula Playground, and it won’t impact your Metric.
With the Formula Playground, you have two options, Auto or Custom. With Auto mode, Dimensions are automatically created based upon your formula. This is a good way to verify whether your Dimensions are aligned.
Break down the formula into smaller parts, hitting enter after each complete section. This allows you to see how each part is working. Auto mode shows the result in the correct Dimensions. You can use this to align the resulting Dimensions with those of your Metric.
With Custom mode, you can adjust the Dimensions to see the different outcomes. This can help you if your formula needs to have specific Dimensions.
🎓 Pigment Academy
To practice using the Formula Playground, visit our Academy activity.
Formula not executing
If you are unsure which function to use, check out Functions by Category
Unique Item values error
.png?sv=2022-11-02&spr=https&st=2025-10-11T10%3A32%3A41Z&se=2025-10-11T10%3A48%3A41Z&sr=c&sp=r&sig=YGflf3rq24Ebkhzcz6vhiw2IAzS%2BU8P%2BtOY7E3nY5zg%3D)
When Unique Item values is toggled on for a List Property, inputting duplicated data results in an error message: “Duplicated data in an unique column”. One way to resolve this is to identify the Property causing the error, provided at the start of the above error message. Copy the formula from the unique column or Property and paste it into the Formula Playground. Then, export the results to Excel and use Conditional Formatting to highlight any duplicate values. After identifying the duplicates, go back into Pigment and remove or correct them in the Dimension List. This should resolve the error.
Check the function syntax
Each function has its own syntax that is needed for it to execute. If you start typing the name of a function, it appears with an f next to it. After selecting or typing in the name of the function, add in an opening and closing parentheses: (). After this, you can hover over the function and the syntax appears below. You can click Learn more to open the documentation on that function.
This might require you to adjust the data type of another Metric or List Property in order for a formula to be executed. If you change the data type or a Metric or List Property you lose that data.
Hidden characters
When copying a formula into Pigment from an external source like an email, Pigment Community, chat, or document, you may encounter errors caused by hidden characters. Line breaks, extra spaces, or other invisible formatting issues are often introduced during the copy-paste process. These hidden characters can disrupt formula parsing, so it’s a good practice to review the formatting of your copied formula to prevent errors occurring.
Review the function’s return data type
From within a function’s documentation page, you see Return type. This refers to the output of a function. It's important to understand the return type to ensure it aligns with your formula. For example, if your formula returns a number data type, and your Metric’s data type is Boolean, you get an error message. Use the Formula Playground in Auto mode to see the return data type, then reformat your Metric to the correct data type.
Format and comment your formula
Use /*comments*/ to test your formula without deleting and copy-pasting parts of it. Also, you can use this to explain what you have done to your colleagues so they have a better understanding of the modeling.
You can also use line breaks to divide your long formulas into sections, making it easier to read and test in sections with formula playground.
ℹ️ Note
You can use a shortcut to comment your formula:
PC:
Ctrl+/Mac:
Cmd+/It behaves as follows:
If no text is selected, it opens a comment with
//.If positioned before text or text is selected, it turns that text into a comment.
If the selected text runs over multiple lines, it turns it into a single comment section, adding
/*to the front and*/to the back.Use the same shortcut to uncomment!