Cohort Metrics on Nested Pivots

[heber_brandao]

Hey team,
Currently I am quite struggling on working with nested pivots and table calculations. Hopefully someone can give me some light on that.

What I am trying to do? -> Calculate Retention Rate on Month 6, with several breakdowns.

Here is how my data is structured:

• Dimensions
  • Conversion Month (rows)
  • Month Diff (Pivot 1) - Those are my cohorts
  • Macro Region (Pivot 2) 
• Measures
  • # New Customers
  • # Customers EoP (End of Period)

The final outcome is to create a table calculation % Retention in which:
- # Customers EoP / # New Customers Month diff 0

${number_of_customers} /
pivot_where(${month_diff} = 0, ${number_of_new_customers})

If I only have the first pivot in place, this calculation works as intended. However, If I want to see % Retention by month diff AND macro region, I am quite unsure how to write this function once a new pivot comes into play.

Does someone have any advice here?