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: 
BrianH
Contributor
Contributor

Help with cumulative distinct counting

Hi all,

I've searched around previous discussions and my apologies if this exact question has been answered.  I have Qlik usage data broken down by month.  What I want to show is how many distinct users per month we have (that's the easy part), but also cumulative distinct users where only new users in a month get added to the running cumulative count.

The formula I have now:
RangeSum(Above( count (distinct [User ID]) , 0, RowNo()))

But this adds each month's distinct user count to the previous month's count and increments likewise.

 

Say the data looks something like this:

Month    User ID
Jan            A
Jan            A
Feb            A
Feb            B

Right now when I use my formula it shows:
Jan     1
Feb     3

But I want it to show:
Jan     1
Feb     2 (i.e. A was already counted in Jan, but B is new in Feb)

Any help would be greatly appreciated!

(Signing off for the weekend but I will check in first thing Monday)

Thanks.

Labels (2)
1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

See below and attached:

 

=RangeSum(
above(
sum(aggr(count (distinct [User ID]),[User ID] ))
, 0, RowNo())
)

View solution in original post

8 Replies
lorenzoconforti
Specialist II
Specialist II

See below and attached:

 

=RangeSum(
above(
sum(aggr(count (distinct [User ID]),[User ID] ))
, 0, RowNo())
)

robert99
Specialist III
Specialist III

Have you looked at this thread

I almost always use AsofPeriod now for accumulations.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

 

BrianH
Contributor
Contributor
Author

Thanks Lorenzo.  I'm going to play around with this as it did not quite work as I had hoped - I have data going back to the beginning of 2019, but your code shows 0 for several months and then a very small uptick in user counts until the last 2-3 months.  Somehow it doesn't look like it starts counting when it should, and when it starts counting the counts are way too low.  This looks like a good start though!

BrianH
Contributor
Contributor
Author

Thanks Robert - I'll give that a shot if I can't get the function to work!

lorenzoconforti
Specialist II
Specialist II

Can you share your dashboard? Might be easier to get it to work

BrianH
Contributor
Contributor
Author

I think I figured it out!  I had to change the load script to sort the incoming data by Date first.  When I did that, the chart started counting correctly starting with the early months and from what I can tell the results are spot-on!

posywang
Creator
Creator


After searching the whole forum for 1 hour. This is exactly the solution that I was looking for. It works perfectly. Thanks!

Alan1997
Contributor
Contributor

For me, this function works, thanks to Robert99

 

Regards