Skip to main content
Solved

Transaction List Properties

  • April 13, 2026
  • 4 replies
  • 46 views
ChrisAHeathcote
Author
Forum|alt.badge.img

Hi,

Iv heard that it is not best practice to use formulas in transaction list properties due to the performance impact. However, Id like to tidy up imported data; extract dates, identify codes, build hierarchies etc. 

With other platforms I would import into a block as is and then manipulate the data in the platform. Would this be advised with Pigment?

Best answer by KeeganSF

Hi there,

Great question! You're absolutely right that using formulas directly on Transaction List properties can have a performance impact, particularly as data volumes grow.

The recommended approach in Pigment is very much in line with what you're used to on other platforms; import your raw data as-is into a Transaction List, and then perform your transformations (date extraction, code identification, hierarchy building, etc.) in Metrics rather than in TL property formulas.

Here's why this matters and some practical guidance:

  • Transaction List properties with formulas are evaluated per row, so on large lists they can become a bottleneck and may even cause timeouts after imports.
  • Best practice is to keep your TL as a clean, raw data store and use Metrics as your transformation layer. For example, aggregate from the TL once into a first Metric, and then reference that Metric downstream rather than having multiple Metrics each reaching back into the TL.

A few additional tips to keep performance in check:

  • Reduce early: Use FILTER and REMOVE at the start of your formula chains to reduce data volume before heavier operations.
  • Preserve sparsity: Avoid patterns like IFBLANK(..., 0) before multiplications — Pigment natively treats blanks as zero in multiplication.
  • Partition large datasets: If dealing with multiple years of historical data, consider splitting into separate Transaction Lists.

For more details, I'd recommend the following resources:

4 replies

KeeganSF
Employee
Forum|alt.badge.img+3
  • KeeganSFEmployee
  • Employee
  • Answer
  • April 14, 2026

Hi there,

Great question! You're absolutely right that using formulas directly on Transaction List properties can have a performance impact, particularly as data volumes grow.

The recommended approach in Pigment is very much in line with what you're used to on other platforms; import your raw data as-is into a Transaction List, and then perform your transformations (date extraction, code identification, hierarchy building, etc.) in Metrics rather than in TL property formulas.

Here's why this matters and some practical guidance:

  • Transaction List properties with formulas are evaluated per row, so on large lists they can become a bottleneck and may even cause timeouts after imports.
  • Best practice is to keep your TL as a clean, raw data store and use Metrics as your transformation layer. For example, aggregate from the TL once into a first Metric, and then reference that Metric downstream rather than having multiple Metrics each reaching back into the TL.

A few additional tips to keep performance in check:

  • Reduce early: Use FILTER and REMOVE at the start of your formula chains to reduce data volume before heavier operations.
  • Preserve sparsity: Avoid patterns like IFBLANK(..., 0) before multiplications — Pigment natively treats blanks as zero in multiplication.
  • Partition large datasets: If dealing with multiple years of historical data, consider splitting into separate Transaction Lists.

For more details, I'd recommend the following resources:

    ChrisAHeathcote
    Author
    Forum|alt.badge.img

    Hi ​@KeeganSF 

    Thanks for getting back to me so quickly. 

    What happens if none of the raw data can be natively mapped to a dimension? Transaction Lists can not be allocated to a metric as a dimension so Im unsure how you can perform transformations in a metric rather than a list property.

    Chris 

    KeeganSF
    Employee
    Forum|alt.badge.img+3
    • KeeganSFEmployee
    • Employee
    • April 15, 2026

    Hi Chris,
     

    You're right that to aggregate TL data into a Metric using BY, you need dimension-type properties on the TL to group by. So if your raw data doesn't map to any existing dimension, you can't skip TL properties entirely.
     

    The practical approach is:

    Map as much as possible at import time — use Pigment's auto-create dimension items feature to map raw codes/identifiers to dimensions during import setup.
     

    Then use simple TL property formulas for remaining mappings — lightweight lookups like MATCH or LASTNONBLANK are generally fine, though keep in mind that even simple formulas can become a bottleneck on very large TLs (millions of rows).

    • Move heavier computation into Metrics — once dimension links are in place, do your aggregations, calculations, and analysis there via BY.
    • Build hierarchies at the Dimension List level — hierarchies are established through Group Properties on Dimension Lists, not via TL formulas or Metrics.

    So yes, importing raw and manipulating within Pigment is a valid approach — just be mindful of where each transformation happens and the size of your TL.

    Hope that helps, if you feel like you’re specific use case requires more insight feel free to submit a Support Ticket and we can take a deeper look!

      ChrisAHeathcote
      Author
      Forum|alt.badge.img

      Hi Keegan,

      Im moving closer to your thinking. 

      If we cant use TL properties in any meaningful way to extract dimension names and codes from text formatted imported properties can we therefore utilise, automatically generated unique IDs and create a new regular dimension. 

      Then use this to create a series of metrics to then build our data model?

      Automatically Generate Unique IDs for List Items 

      Ask your question here!

      Terms & ConditionsAccessibility statement