There are many tips that exist that can help you ensure your formula is correct. I’ve listed some of them here to help you find the answer. 

Getting a wrong result in a formula or not being able to get a formula to work can be a frustrating experience.  This article is going to give you a few tips to help you troubleshoot your formulas.  I highly recommend reading all the tips because they are beneficial but we have broken them down into two categories, not getting the expected value and not being able to get a formula to execute due to getting errors.

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

Multi-dimensionality in formulas

Pigment is multi-dimensional, meaning that your Metrics are built over one or many Dimensions (like Country, Months, Cost Center...). It’s important to understand that when you want to cross Metrics together, they have to be “dimensionally 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 will 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 multi-dimensionality. 

Check the Block’s 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 dimensionality 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 will lose any data in that Metric.  Understanding the Metrics’ dimensional structure is important to ensure your formula’s results are aligned. Use the Formula playground in Automatic mode to see the dimensionality output of your formula.

Check the dimensionality of any referenced Metrics

Hover over each Metric in your formula to see its Dimensionality of it.  If the dimensionality of the metric is 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 dimension, your values might be 12 times higher, depending on how many years you have set up. This might be a case where you use a modifier to adjust the dimensionality of a metric in your formula. The identifying of dimensionality of your referenced or source metrics and your current or target metrics is called Source to Target Mapping.  You can learn more about source to target mapping and when you can use a Modifier to adjust the dimensionality in this article. 

️‍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 dimensionality is automatically created based upon your formula.  This is a good way to verify whether it is dimensionally aligned. 

Break down the formula into smaller parts, hitting enter after each complete section.  This will allow you to see how each part is working. Auto mode will show the result in the correct dimensionality.  You can use this to align the resulting dimensionality with the dimensionality of your metric. 

With Custom mode, you can adjust the dimensionality to see the different outcomes.  This can help you if your formula needs to meet a specific dimensionality. 

Formula not executing 

Unique Item values

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 will appear 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 will appear 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 will 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 will see a 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 will 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. #BestPractices

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. 

Get more help

Search the Knowledge Base, Ask the Community, or Submit a Support ticket 😉