Help with cumulative distinct counting

[BrianH]

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.

[lorenzoconforti · Accepted solution]

See below and attached:

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

[robert99]

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]

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]

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

[lorenzoconforti]

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

[BrianH]

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]

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

[Alan1997]

For me, this function works, thanks to Robert99

Regards