Top Questions of the Month - August

Month after month, Pigmenauts share challenges, ideas, and solutions that help us all learn and grow together.

To celebrate this knowledge sharing, here are August’s top 5 questions. We’re highlighting the ones that sparked the most interest and views from fellow members 💡.

Whether you faced the same challenge or want to learn new tips, these threads are a great place to explore, contribute, and connect.

1. Creating New List Items from a Transaction List

Question - ​@JF.LM:

“I need to automatically create new List items from a Transaction List, but importing doesn't allow me to map a formula field. Is there another way to achieve this?”

Best Answer - ​@Julie Guérin:

You can't generally create new List items directly from a formula field during an import from a Transaction List, as the import needs a concrete mapped value.

As a workaround, create a unique ID text property in the Transaction List and use it as the source for the new List item. If the unique ID is formula-based, create a second unique ID property in the destination List and map it to the text property.

JF.LM later shared another working approach using the Transaction List's unique ID, a formula-generated key, and a property using the RANK function, combined through a Pigment-to-Pigment import to ensure uniqueness.

Read the full discussion here:

2. Do table calculations have to apply to all metrics in that table?

Question - ​@johnturner:

“I have a YTD Calculated Item applied across several financial metrics and a headcount metric. While the financial metrics should cumulate, I want headcount to show the closing value instead. Do Calculated Items have to apply the same calculation across every metric?”

Best Answer - ​@Veniamin:

No, you can configure a Calculated Item differently for individual Metrics within the same Table.

Right-click the cell where the Calculated Item and Metric intersect and select Edit Calculation for this Metric. This lets the financial metrics continue to cumulate while applying a different calculation to headcount.

Changing headcount's default aggregator to Last won't affect a YTD Calculated Item because Cumulate operates independently of the Metric's default aggregator.

Read the full discussion here:

3. Combining a cumulate (YTD) metric with an Offset vs LY metric

Question - ​@Sander Booij:

“I have a YTD cumulative calculation and an Index vs Last Year calculation that both work separately. How can I combine them to calculate YTD actuals against YTD last year?”

Best Answer - ​@Veniamin:

You can perform both operations within one Calculated Item instead of stacking two separate calculations.

Keep the Index LY calculation as a percentage ratio with the comparison item offset by the previous 12 months, then turn on Cumulate value, set the dimension to Month, and reset by Year.

This cumulates both sides before calculating the ratio, giving you YTD Actuals vs. YTD Last Year rather than a cumulated ratio.

Read the full discussion here:

4. Google Sheet Connector - Moves Manual Content

Question - ​@Charlie Partington:

“My Pigment Google Sheets Connector exports data starting in column B, while column A contains custom formulas. Each refresh pushes those formulas below the imported data instead of keeping them alongside it. How can I prevent this?”

Best Answer - ​@Veniamin:

The target cell only determines where the exported Block starts. When refreshed, the Connector resizes the sheet to match the View, which can also move content in adjacent columns.

The recommended workaround is to give the Pigment Connector its own dedicated tab, then use a second tab for your calculations and reference the imported data there. Using dynamic formulas such as ARRAYFORMULA can allow those calculations to automatically grow and shrink with the exported data.

Where possible, another option is to move the calculation into a Pigment Metric and include it directly in the exported View.

Read the full discussion here:

5. Export a view as CSV

Question - ​@Nancy Mehrotra:

“When I download a View as CSV manually it looks correct, but when I export it through the API to another tool, the structure looks different and I'm only seeing Dimension names as headers. Why?”

Best Answer - ​@Veniamin:

A CSV downloaded from the Pigment UI and an Export API response aren't structured in the same way. The API returns flattened, unformatted data, with Dimensions represented as columns and their items appearing in the rows below.

Also check the field delimiter: the API uses semicolons by default. If the receiving tool expects commas, you can use ?fieldDelimiter=Comma to change it.

For integrations with a warehouse or similar downstream system, a raw Block export may also be a better fit than exporting the View.

Read the full discussion here:

These questions reflect the curiosity and creativity of the Pigment Community, and your insights can make them even more valuable. If you’ve faced similar challenges, drop your ideas, solutions, or tips in the threads.

Have your own question? Start a new thread and let the community help!