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' ,



      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?