9 Replies Latest reply on Apr 26, 2018 5:52 AM by Deborah Galvani

# Empty spaces on Cohort analysis

Hi everyone!

I'm trying to create a Cohort Chart for my client. But there's some considerations making this task really hard.

Here's my scenario:

I created a two calculated fields:

Data da primeira compra (in english: first purchase date)

{ FIXED [Nome do cliente] : MIN([Data da compra])}

Meses desde a primeira compra (in english: months since the first purchase)

DATEDIFF('month', [Data primeira compra], [Data da compra])

I used the field "Data da primeira compra" on rows with DATETRUNC('month', [Data primeira compra]), the field "Meses desde a primeira compra" on columns and the measure "Vendas" (Sales in english) on text, as you can see below:

I need to display the running sum of this measure:

But I can't display the values on all cells, because I have to follow the Cohort logic (n_columns - row_index), then I created this calculated field:

Venda acumulada (in english: Running sales)

IF LOOKUP(ATTR([Meses desde a primeira compra]),0) <= MAX({FIXED [Nome do cliente]: MAX([Meses desde a primeira compra])})

then

RUNNING_SUM(SUM([Vendas]))

end

and my grid became this:

It seems like the empty cells (image below) occurs because there isn't any rows with this value, not even with null.

How can I populate this empty cells with the running sum, but still making it stops when it should? (Cohort logic i talked about before).

I'm leaving the workbook attached.

Cheers!

• ###### 1. Re: Empty spaces on Cohort analysis

Hi, Giorge

Not sure if I understand correctly or not, but if you add the else clause in your calculation as shown below screenshot, you will get all cell filled.

Hope this helps

ZZ

• ###### 2. Re: Empty spaces on Cohort analysis

Thank you for your reply, Zhouyi, but it doesn't solve my problem.

I need to keep the Cohort Chart format, like the image below:

• ###### 3. Re: Empty spaces on Cohort analysis

Hi, Giorge

Below is the screenshot of the result

Hope this helps and let me know if you have question

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Empty spaces on Cohort analysis

Thank you, Zhouyi! It worked!

• ###### 5. Re: Empty spaces on Cohort analysis

Hi Zhouyi Zhang!

I have the same problem and when I do the process again the cohort looks like this:

And in addition in that same file that you made available when I take the "venda acumulada" field and put it back, the cohort goes out of its format

Are there any settings in the table calculation to make the cohort format?

I could not find it, can you help me?

Thanks!

• ###### 6. Re: Empty spaces on Cohort analysis

Hi, Deborah

I think you need to reset the table calculation as they all related in this case.

If you still stuck somewhere, please share the workbook of where you are.

ZZ

• ###### 7. Re: Empty spaces on Cohort analysis

I tried to reset the table calculation but I still could not do the cohort format.

Tks!

• ###### 8. Re: Empty spaces on Cohort analysis

Hi, Deborah

You need set the table calculation for v_index inside the venda acumulada and you can see this from the drop down of the table calculation as shown below

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 9. Re: Empty spaces on Cohort analysis

Thank you for the help, it worked!