Count Duplicates for each Duplicate Row Cumulatively

[lampshadedreams]

So I have a table similar to the one below:

Date	ID	Count Duplicate
01-Jan-2019	X	2
12-Aug-2019	X	2
02-Feb-2019	Y	4
08-May-2019	Z	1
07-Jul-2019	Y	4
22-Dec-2019	Y	4

I used the following expression to get the third column:

=Count(TOTAL<ID> DISTINCT Date)

However, I need a fourth column to show me the cumulative number of duplicates based off order of the date:

Date	ID	Count Duplicate	Count Duplicate Cumulatively based off date
01-Jan-2019	X	2	1
12-Aug-2019	X	2	2
02-Feb-2019	Y	4	1
08-May-2019	Z	1	1
07-Dec-2019	Y	4	3
22-Jul-2019	Y	4	2

How would I formulate an expression to create the fourth column?

[Arthur_Fong · Accepted solution]

There you go:

Create the cumulative logic at script level using peek() function.

First, sort your data by ID, then Date.

Then apply peek function to get the result as below:

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

[NZFei]

Isn't this all you want? Make sure the dates are numbers behind the scene, not texts.

Also sort order must be ID first, and then Date.

[lampshadedreams]

That worked great thank you so much!