
1. Re: Weighted avg calculation
Chris Hastie May 23, 2018 7:57 AM (in response to Ayoub Briki)Hi Ayoub,
You should be able to calculate your weighted average in a similar fashion to your Excel calculation, using the following formula:
SUM( [Retention Rate] * [Cohort Size] )
/
SUM([Cohort Size])
You attached image shows the retention rates as figures in a table, however your comment regarding table calculations leads me to suspect that these figures are calculated? If so, I would expect your final solution to use some form of Level of Detail Expressions.
Are you able to share either a sample dataset or a copy of your calculation for retention rate?
Cheers,
Chris
InterWorks

2. Re: Weighted avg calculation
Ayoub Briki May 23, 2018 8:37 AM (in response to Chris Hastie)Hey Chris,
Thank you for your prompt reply.
The retention rate is also calculated in the workbook. Here's how : ZN([nbr_users]) / LOOKUP(ZN([nbr_users]), FIRST())
And the cohort size is equal to : COUNTD([user_id])
Does that help ?

3. Re: Weighted avg calculation
Chris Hastie May 23, 2018 9:06 AM (in response to Ayoub Briki)Hey Ayoub,
That does help. I've mocked up a possibility in the attached workbook. Without access to a sample workbook I can't be sure if I'm doing the right thing, however my hope is that this sparks the missing piece for you. My main suggestion is to calculate Retention Rate using an EXCLUDE LOD calculation instead of a LOOKUP table calc.
Please take a look and let me know your thoughts.
Cheers,
Chris
InterWorks

4. Re: Weighted avg calculation
Ayoub Briki May 23, 2018 9:12 AM (in response to Chris Hastie)Thanks again Chris.
That's also what I could achieve, however, the requirements are different ; during the months where there's no full data (younger cohorts) the weighted average should be based on the columns with available data only.
Coming back to your sample workbook, in the weighted average of the 5th active month It should be 12% instead of 1% .

5. Re: Weighted avg calculation
Chris Hastie May 24, 2018 2:35 AM (in response to Ayoub Briki)Hi Ayoub,
How does the attached look?
Cheers,
Chris
InterWorks