Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi Gysbert,
This doesn¨t provide a clear solution for my case especially because I am working with Qliksense
Thanks
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))
The script and expressions syntax is the same for Qlikview and Qlik Sense. The As-Of table will work equally well in both.
See my first post in this discussion. Rangesum is not going to work. You will have to resort to using an As-Of table.
Ok thank you for your contribution
I will try the as of table