Calculating the percentage difference against first column in a Pivot Table

Question & Answer
, ,
1

I’m trying to calculate retention on a quicksight table but I’m unable to calculate the percentage difference against a fixed value. This is the view of my current pivot table. I want to calculate the retention percentage so that would be customers retained / original number of customers where customers retained is the count at each month after launch and original number of customers is the count at month 0. I want to do this based on cohort date. So for example on my current table for the cohort on Jul 1, 2018, column 0 would have 100%, column 1 would have, 100%, and column 2 would have 50%.
image

I saw this other post that is similar to mine but I can’t figure it out Calculating the percentage difference against the initial distinct count and not the previous column in a Pivot Table

2

Hello @Valeriehernandez , welcome to the Quicksight community!

Have you tried using the the periodOverPeriodPercentDifference?

If you give that a try, for offset, try saying offset of 1 for the first column and offset of 2 for the second column.

Let me know if that helps!

3

If I use that, would I have to manually change the offset per column? I would want them all to compare to column 0, but I don’t know how many columns I would have (could be anywhere from 3 to 32 columns). Would there be a way to always compare to the same column?

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!