Skip to main content

Hi Pigment Community,

I am reaching out to bring attention to an issue I've been encountering with Pigment, particularly in relation to the mathematical output it provides. While Pigment generally performs as expected, I have noticed instances where the results are unexpectedly incorrect.

To provide clarity, I have attached screenshots displaying the metrics and their corresponding outcomes. I've highlighted the specific instances where Pigment has generated results that deviate from the anticipated values. It's worth noting that there are occurrences where the formula yields accurate results, but the inconsistency raises concerns about best practices and how formulas work. 

My hope is that you can help me understand the root cause of this issue and provide insights to correct this issue. I appreciate any assistance you can provide in shedding light on why Pigment might be producing these unexpected results. Additionally, I am keen to learn any best practices or recommendations to avoid similar discrepancies in the future.

Both metrics used to calculate the ratio. The expected answers for France, xs/small, inbound, are provided on the left. Color coordination is provided for easier reading.
Outcome of both metrics being divided by one another

By looking at the image above it can be seen that out of all the highlighted values only purple is correct, the rest are not close at all to being correct. What’s even more interesting is that when looking at Nov 22 and Dec 22 they both have opportunities of (9) and customers of (5), but in the end they have different percentages. How can this even be possible?

Extra Info

  • The metrics are built using a transaction block called ‘All Deals’
  • The dimensions in the formula are called using the ‘by’ aggregator. Additionally all rows of these dimensions are fully filled, that is to say they have no blank rows. 
  • When I drill down to see the data in ‘% opp to customer’ I see the same values displayed in the first screenshot with the count of opportunities and customers. 
  • the ‘Actual old’ filter is used to ensure the calculation only takes place on data according to our switchover date, which is based on our max date in ‘All transactions’
  • IF(ISDEFINED('€ # ] New Customer by create date - base'mREMOVE: Month]), IFBLANK('R # ] New Customer by create date - base', 0)) → this formula is used for ‘new customer by create date’ to counter pigments sparse model. This makes the formula accurate, because without this if there was an opportunity that yielded 0 customers pigment would consider the percent as blank instead of 0% so my formula ensure that my % calculation will have 0% rather than just blanks. 

I’d love to understand why this is happening, so if you require any further information to better understand the situation please let me know!

I appreciate all the help and guidance you can provide. 

Thank you!

-Darious

Hi @darious ,

I believe this falls into the same issue of your previous question around Count Blank over Count All with dimensiosn.
As I see in your screenshot the dimension Pipeline Simplified is showing All, so Pigment is actually applying na aggregation of the division applied at the granular level. In other words, the last metric is dividing at the lowest level (for a each item of the Pipeline Simplified) but applying the Sum when it outputs for the Total (you can check the sum symbol to verify the applied aggregation method).
This could be verified as well if you pivot the Pipeline Simplified into the rows.

In order to avoid you can use the same logic showcased in the community article: How to calculate ratios and averages at summary levels. Where you would add the two metrics and apply a calculated item.
If you need the Aggregated result for future calculations, I would suggest creating a metric without the Pipeline Simplified dimension.

Hope this answers your question and clarifies the aggregations when using ratios and averages.

Issam

Hi Darious,

Firstly, thank you very much for the detailed question - It really makes our lives so much easier when trying to provide you with the best advice possible!

To best assist you, I will create a ticket for you in our support portal so that I can look into this within your model. Once I’ve found a solution for you, I’ll make sure to share it here as well, so that everyone in the community can benefit from this knowledge.

Speak to you soon!

- Mat

Reply


Ask your question here!

Terms & ConditionsCookie settings