9 Replies Latest reply on Mar 3, 2016 1:33 AM by Mark Fraser

# Creating cohort analysis graph using average of averages

Hi,

I'm still learning Tableau and trying to figure out how to create a cohort analysis graph. Ultimately, I'm trying to show the average total sales by cohort over time. For instance, I have the following data:

Sale IDSale AmountSales RepTime Since HireCohort
1\$100Joe1 month1
21\$150Joe1 month1
3\$200Rick1 month1
4\$50Joe2 month1
5\$75Jane1 month2
6\$200Jane2 month2
7\$150Jane2 month2

I need to:

1. Sum total sales by Rep by Month (ex: @ 1 month...Joe = \$100 + \$150; Rick = \$200; Jane = \$75)

2. Average total sales by rep by month by cohort (ex: cohort 1 @ 1 month.... (\$250 + \$200) / 2)

3. Plot the data where X-axis = Time since hire, Y-axis = Avg Total Monthly Sale, and each line represents the different cohorts (i.e., the average of the sales reps within a cohort)

• ###### 1. Re: Creating cohort analysis graph using average of averages

Hi Joyce

Do you have v9?

• ###### 2. Re: Creating cohort analysis graph using average of averages

I have 9.2

• ###### 3. Re: Creating cohort analysis graph using average of averages

Perfect!

So... Here is 1

{FIXED [Sales Rep],[Time Since Hire] : SUM([Sale Amount])}

• ###### 4. Re: Creating cohort analysis graph using average of averages

2... although I'm not 100% sure, its certainly right for your month 1 example, but what about Jane in month 2, cohort 2?

{FIXED [Time Since Hire],[Cohort] : SUM([Sale Amount])/COUNTD([Sales Rep])}

• ###### 5. Re: Creating cohort analysis graph using average of averages

Here is the workbook v9.2.4

I am not so sure on 3, is it meant to be the same monthly average calculation, or a different one?

Hopefully you have enough there to get you started!

Cheers

Mark

• ###### 6. Re: Creating cohort analysis graph using average of averages

Hi Mark - thanks for your help.

For #3, I essentially want to plot your table in #2 as a line graph...where Time Since Hire = X-axis.  The Sales = Y-axis. Each cohort would have it's own line. This example, we would have 2 lines.

• ###### 7. Re: Creating cohort analysis graph using average of averages

Hi Joyce

Your explanation is fine, its obviously me...

Like this?

I wondered if this may better tell the story...

Both in the attached.

Cheers

Mark

• ###### 8. Re: Creating cohort analysis graph using average of averages

Thank you so much Mark!!! This is definitely a great start for what I need. If I only need the graph, it seems I really only need the 3rd sheet.

• ###### 9. Re: Creating cohort analysis graph using average of averages

You're very welcome, pleased we got there!

The reason I initially asked about your version... the above uses LOD expressions (you can tell that because of the { } and the format)

If you're continuing with Tableau, i would strongly advise reading up on them, they are extremely powerful for the kind of things you wish to do

And of course, please use our forums - plenty of really good people and lots of interesting resources!

Best of luck!

Cheers

Mark