4 Replies Latest reply on Nov 18, 2015 11:46 PM by Amos Mbugua

# Cohort Analysis

Hi,

I need to replicate the following calculations on excel in tableau with regards to churn calculations. Am mostly interested on the rows which is sales for each cohort group for month 1,row 2 will sales across the cohorts for month 2 and so on

• ###### 1. Re: Cohort Analysis

There are no calcs that I see in the attached spreadsheet? Can you share the raw data? I'm sure that the chart in the excel file can be duplicated.

• ###### 2. Re: Cohort Analysis

Hi Mark,

Sorry for the delayed response. I have attached workbook with the data.

Instead of having the actual months of order on the rows, I would like to have Month 1,month 2 and so on. This will enable me to average  month 1 for all customers who started in different months.

• ###### 3. Re: Cohort Analysis

Maybe a calculation like this, on both dates would do the trick? I'm guessing to normalize it for each customer you need to check to see if there was an order in the given month to make it their true "Month n"?

Hopefully this can get you started on a path.

```If [Date Order] = { FIXED [Customer] : MIN(([Date Order])) } Then "Month 1"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+1 then "Month 2"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+2 then "Month 3"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+3 then "Month 4"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+4 then "Month 5"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+5 then "Month 6"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+6 then "Month 7"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+7 then "Month 8"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+8 then "Month 9"
ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+9 then "Month 10"
END
```
1 of 1 people found this helpful
• ###### 4. Re: Cohort Analysis

Thanks Mark.