Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Cumulative count of distict IDs per month

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?

6 Replies
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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Gysbert,

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

Thanks

Anonymous
Not applicable
Author

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.


talk is cheap, supply exceeds demand
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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Ok thank you for your contribution

I will try the as of table