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

    Empty spaces on Cohort analysis

    Giorge Luiz

      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])})






      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.