4 Replies Latest reply on Nov 22, 2016 5:58 AM by Marcin Zwierz

# How to calculate daily averages of a retention matrix?

Hi guys,

I created a simple retention matrix using LOD function and then tried calculating average retention per day using Analysis -> Totals -> Show Column Grand Totals but I get a result, which is clearly incorrect:

It seems that Grand Total clashes with the LOD function. Naturally, I tried solving this problem with different calculated fields but with no luck. I would very much appreciate any help!

Thanks,

Marcin

• ###### 1. Re: How to calculate daily averages of a retention matrix?

Hi,

What is the expected result? Maybe you simply want different addressing?

• ###### 2. Re: How to calculate daily averages of a retention matrix?

Hi Łukasz,

preferably in the "Grand Total" row I would like to see the average retention calculated for each day. So for example for day 10 I would like to see 100% (or an empty cell because it's obvious that retention on the day of acquisition is 100%), for day 11 I would like to see (100% + 2%)/2 = 51% and so on. I tried using some tricks from Cohort Analysis - How to sum and average only valid values in table? but these do not work in conjunction with the LOD function..

• ###### 3. Re: How to calculate daily averages of a retention matrix?

OK, attached are 2 solutions:

1. Using your table calculations and customized totals:

2. Easy way - although LoD will affect performance more - by using LoD in the retention calc and optional aggregation for total:

Łukasz

• ###### 4. Re: How to calculate daily averages of a retention matrix?

That's super easy, works like a charm! Thank you so much!