5 Replies Latest reply on Feb 7, 2017 9:59 AM by Yuriy Fal

# Cohort Summaries by Retention Month

I've seen (and used) a few posts about creating cohort analysis workbooks in Tableau and have been successful in pulling together the attached based on instructions found here in the community.  I'm trying to take this one step further by calculating an average and weighted average for each retention month, and would appreciate a bit of help from the more advanced users here.  I've attached a workbook with an extract of sample data to support a cohort analysis similar to one found here.  Also attached is an excel spreadsheet that contains an export of the data in Tableau, with rows added to calculate an average and weighted average by retention month.  The latter uses sumproduct and sumif formulas to only include those values where months exist.  I would greatly appreciate any suggestions on how to do what I've done in Excel within the Tableau workbook.

• ###### 1. Re: Cohort Summaries by Retention Month

Hi Bonnie,

May be this could help

Cohort Analysis - How to sum and average only valid values in table?

Or this

Re: Get percentage of cohort instead of simply 'percent of total'

Yours,

Yuri

• ###### 2. Re: Cohort Summaries by Retention Month

I have looked into the first suggestion previously but it does not work when applied to the data - at least not in the manner I would expect.  I'm not sure how the 2nd applies.  It may be a lack of understanding on my part, so I've uploaded a new copy of my workbook with an additional sheet containing the calculated fields suggested.

At a minimum, I am looking for 77.98% as the average in the 11th month.  Per the first suggestion in the proposed answer, I added a copy of the Launch Date to the Detail shelf/button.  The Grand Total field displays all the possible values in the columns above, not a sum or an average.  (See Applied Launch Date Copy Detail tab).

On the Applied Total & Relative tab, I removed my own calculated field and walked through the other steps for breaking out the table calculations for Start Val, Start Val Total, etc.  The Relative V2 calculation does not sum or average the columns as needed.

• ###### 3. Re: Cohort Summaries by Retention Month

Hi Bonnie,

It's all about an "make-it-looks-like-an-Excel" stuff,

so one outta use (nested) Table Calculations a lot.

I've simplified your calcs a bit -- using SUM(1)

which is the same as SUM([Number of Records]),

and using WINDOW_SUM() instead of TOTAL()

in every "inner" Table Calc to allow nesting of calcs.

Besides, there would be more than one Grand Totals

(both AVG & Weighted AVG at least, others would come),

so a single-view layout (with only one Custom GT) doesn't work,

and one outta use a "two-worksheets-on-a-dash" approach --

with GT table on it's own sheet.

Please find the attached workbook (version 9.3, sorry.

If you're using TD 9.0 only, you could unzip the *.twbx

and replace every occurrence of version = '9.3' string

to the version = '9.0' one in the workbook XML code).

Hope this could help a bit.

Yours,

Yuri

• ###### 4. Re: Cohort Summaries by Retention Month

Yuri, thank you so very much for your instructions!  I suspected that I needed to simplify my existing calculations in order to do this, so I thank you for clarifying this and provided the updated example.  I have plugged this into my live version and am getting the results expected.  Thanks again for your help!

• ###### 5. Re: Cohort Summaries by Retention Month

Bonnie, you're welcome.