Calculating the percentage difference against first column in a Pivot Table

Question & Answer
, ,
4

Hello @Valeriehernandez , I’m sorry to get back to you so late on this!

Are you still running into this issue? I think the solution in this post is your best bet rather than trying to use percent difference.

To break down the solution, you could do the following:

  1. Create this calculated field > ifelse(truncDate('MM',{cohort_date})<=truncDate('MM',execution_month_after_launch),1,0)

2 Create this calc > maxover(dateDiff({cohort_date},date,'MM')+1,[user, truncDate('MM',execution_month_after_launch)], PRE_AGG)

  1. Create this calc > rank([ifelse({Calc 1}>0,user,null) ASC, truncDate('MM',date) ASC],[user], PRE_AGG)

  2. Create this calc > distinct_count(ifelse({Calc 2}={Calc 3},user,null))

  3. Create this calc > maxOver({Calc 4},[truncDate('MM',{cohort_ date})],POST_AGG_FILTER)

  4. Create your final calc >
    {Calc 4}/{Calc 5}

You will have cohort_date in your Row field well, execution date month in the Column field well, and then Calc 6 in your Value field well.

Let me know if that helps!