topic Re: Count Duplicates for each Duplicate Row Cumulatively in QlikView

Count Duplicates for each Duplicate Row Cumulatively

lampshadedreams — Fri, 06 Sep 2019 00:43:47 GMT

So I have a table similar to the one below:

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 — Fri, 06 Sep 2019 02:37:50 GMT

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 — Fri, 06 Sep 2019 04:25:10 GMT

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 — Fri, 06 Sep 2019 14:31:54 GMT

That worked great thank you so much!