Cumulative count of distict IDs per month

[Anonymous]

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?

[Gysbert_Wassenaar]

The problem is that you're summing counts per month. What you actually want is a count distinct over a range of months. But using rangecount instead of rangesum won't help either. There is however another solution that will work: the As-Of table. See The As-Of Table and Calculating rolling n-period totals, averages or other aggregations

[Anonymous]

Hi Gysbert,

This doesn¨t provide a clear solution for my case especially because I am working with Qliksense

Thanks

[Anonymous]

Hi Gysbert,

I did this but it still doesn't work. Do you think there is any problem in the script

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

ID)),0,RowNo())),ID))

[Gysbert_Wassenaar]

The script and expressions syntax is the same for Qlikview and Qlik Sense. The As-Of table will work equally well in both.

[Gysbert_Wassenaar]

See my first post in this discussion. Rangesum is not going to work. You will have to resort to using an As-Of table.

[Anonymous]

Ok thank you for your contribution

I will try the as of table