4 Replies Latest reply on Nov 13, 2018 6:32 AM by Paul Wachtler

# Cohort analysis

Hi everyone

I'm struggling with some cohort analysis.  I've read the other guidance I can find but my problem is slightly different.

I want to show cumulative no. of lesson hours taken by our learner drivers over x months since first purchase date divided by distinct no. of total learners for the cohort.  Each cohort is grouped based on month of first purchase.

The output will ideally show me the total no. of hours on average a person in each cohort has taken from first purchase date until now.

I am able to display the cumulative total hours in the x months since purchase easily enough but I run into issues when trying to divide that by the no. of distinct learner in each cohort.

Instead of dividing each cell by the total no. of learners in the cohort, Tableau divides each cell by the learner count for that specific month since purchase.

Any help is massively appreciated.

Thanks

Lambro

• ###### 1. Re: Cohort analysis

Hi Lambro,

If you can upload your Tableau workbook, that'd help.  But I think you're looking for something like this (obviously make sure to rename the fields to what you have them named in your workbook).

First calculate your cohorts - this will give you the very first order date for each driver.

Field Name: [First Purchase]

{fixed [driver]: min([purchase date])

If you want to cohort that into months, you can use:

datetrunc('month',[First Purchase])

So now you'll have all of your drivers cohorted into the first month they made a purchase.  Drop that onto your rows shelf so each cohort has it's own row.  Then drop the class month, or whatever you're calling your other month field onto column.  You can then create a calculation that divides the total number of hours by total drivers and drop that onto your marks card - it'll separate out by cohort and class month.

That should give you what you're looking for.  Like I said, if you can attach a workbook for this it'd be helpful.

Best,

Paul

1 of 1 people found this helpful
• ###### 2. Re: Cohort analysis

Thanks Paul

The workbook is commercially sensitive - is there a way I can share without giving access to underlying data or perhaps I can create a dummy data-set to understand the principle.

I've managed to get as far as the calculation "You can then create a calculation that divides the total number of hours by total drivers and drop that onto your marks card - it'll separate out by cohort and class month."

I can create a cumulative count of learner hours using the quick calculation >> running total field.  I'm not sure how to then divide that by total learners for the cohort? Would you use secondary calculation or should I pop the entire formula into a calculated field including the running total?

Thanks again

Lambro

• ###### 3. Re: Cohort analysis

How to divide a column of data by grand total?   I think this might be the ticket.

• ###### 4. Re: Cohort analysis

Hi Lambro,

After you completed the first two steps, you should have a view with the class month across the columns, and the driver cohort months down your rows.  I was suggesting a calculation where you take your lesson hours field and divide it by the count of drivers, something like this:

sum([lesson hours]) / countd([driver])

That will average out the numbers per person.  Take that calculated field and place it on your label mark.

If you're still unclear, it would be helpful if you could put together some dummy data so we can mockup what this looks like.

Best,

Paul