Cohort Analyses: Show Orders as Number Of

Hi Guys,

For a project, I am working on a Cohort analyses, which should show the avg number of orders over the subsequent months based on the first month. Let me explain it with pictures. The first picture shows what I have now, and the second picture shows how it should be.

Screen Shot 2017-08-18 at 18.00.29.png

Screen Shot 2017-08-18 at 17.59.52.png

So, basically I want to show the average of order number of the new customers who orderd something in the first month and how much these customers ordered on average over the subsequent months following. So, the average should be above 1 one in the first month and drop a lot in the months afterwards. However, I have problems with finding the right settings for the Pivot, as the data shouldn't be % of the total. It should be the avg number of orders over the months based on the first month. Hence, it should decrease over the months, as the customers are leaving as time passes by.

Screen Shot 2017-08-18 at 18.03.23.png
This is the input data for the pivot.

Hopefully my question is clear and hopefully someone can help me out, as I am stuck witht he problem for several days already..

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.