6 Replies Latest reply: Nov 23, 2017 8:21 AM by Peter Alao RSS

    Cumulative count of distict IDs per month

    Peter Alao

      Good day all,

       

      I would like to cumulate distinct IDs per month in a pivot table. Here is my script for the month seperation which works flawlessly:

       

      If(num(Month(START_DATE))<=3 and Year(START_DATE)=2017 or Year(START_DATE)<2017, 'Mar 2017',Month(START_DATE) & ' ' & Year(START_DATE))


      This cumulates all measure till March 2017, then subsequent months.


      Now I would like to cumulate distinct count of some IDs per month in the columns. I used this script:


      rangesum( above(Count(distinct if(STATUS ='COMPLETED' and NAME = 'ENT_Inception' ,

      ID)),0,RowNo()))

       

      This worked in cumulating all the values distinctly for every month but the problem is that there are some IDs which repeat in all the months, the DISTINCT function couldn¨t capture this.

       

      Could anyone help with this?