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

      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!