Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lampshadedreams
Contributor III
Contributor III

Count Duplicates for each Duplicate Row Cumulatively

So I have a table similar to the one below:

DateIDCount Duplicate
01-Jan-2019X2
12-Aug-2019X2
02-Feb-2019Y4
08-May-2019Z1
07-Jul-2019Y4
22-Dec-2019Y4

 

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:

DateIDCount DuplicateCount Duplicate Cumulatively based off date
01-Jan-2019X21
12-Aug-2019X22
02-Feb-2019Y41
08-May-2019Z11
07-Dec-2019Y43
22-Jul-2019Y42

 

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

 

1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

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:

clipboard_image_0.png

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

View solution in original post

3 Replies
Arthur_Fong
Partner
Partner

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:

clipboard_image_0.png

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

View solution in original post

xufei123
Partner
Partner

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.

clipboard_image_0.png

lampshadedreams
Contributor III
Contributor III

That worked great thank you so much!