Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Counting changes in values of a field

Can't seem to figure this out. I have weekly (Friday) snapshot data on over 500 Projects with a Status field. My data is one .xlsx file that I keep concatenating to every week. My data spans all of 2019 to date (1/24/20).  

There are over 15 statuses, but I am trying to count how many projects have switched to Complete status in 2020. There are currently 42 projects that have a Complete status, but most of them have become Complete in 2019. 

How can I count when Status field changes to Complete (from any other status)? Should/can I do this at chart level, or in load script?

Sample data of one project: (altered for simplicity)

PROJECT IDDATESTATUSCOMPLETION DATE (NEW FIELD)
AD23111/29/2019Testing-
AD23112/6/2019Testing-
AD23112/13/2019Awaiting Confirmation-

AD231

12/20/2019Awaiting Confirmation-
AD23112/27/2019Awaiting Confirmation-
AD2311/3/2020Awaiting Confirmation-
AD2311/10/2020Complete1/10/2020
AD2311/17/2020Complete-
AD2311/24/2020Complete-


That project should be in my count, since it became Complete on 1/10/2020.  This project should NOT be counted....

PROJECT IDDATESTATUSCOMPLETION DATE (NEW FIELD)
AD18811/29/2019Awaiting Confirmation-
AD18812/6/2019Awaiting Confirmation-
AD18812/13/2019Complete12/13/2019
AD18812/20/2019Complete-
AD18812/27/2019Complete-
AD1881/3/2020Complete-
AD1881/10/2020Complete-
AD1881/17/2020Complete-
AD1881/24/2020Complete-


.....Since it became Complete in 2019.

Thank you!!

1 Reply
Highlighted
Contributor III
Contributor III

Re: Counting changes in values of a field

I just realized what would be perfect for me.  Ideally if I can create a Field in the load script that copies the Date when Complete appears for the first time.
New field could be called Completion Date, and be null except for the one date where Complete appears the first time.
So in top chart my Completion Date would have 9 null values and have 1/10/2020 on the 7th row where it became Complete.