Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dtate1959
Contributor III
Contributor III

Determine the number of days between a change of State

I have a table of project names , change dates and states. I need to be able to determine the number of days between the change of states by project. So, as an example, if Project Test1 Changes from Active on 1/1/2019 to On Hold on 1/6/2019 i need to be able to determine the count of days between those two changes of state as 6 Days.

 

Sample data:

ChangeDateProjectNameState
1/1/2019Test1Active
1/1/2019Test2Active
1/1/2019Test3Active
1/6/2019Test1On Hold
1/8/2019Test2Active
1/10/2019Test1Active
1/12/2019Test3On Hold
1/12/2019Test2On Hold
1/13/2019Test1On Hold
1/16/2019Test1Active
1/16/2019Test2On Hold
1/16/2019Test3On Hold
1/18/2019Test3Active
1/20/2019Test2Active
1/17/2019Test1Closed

 

 

I am relatively new to using Aggr and Rank functions, so, any help is appreciated.

Thanks,

DT

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

May be something like this?

 

Aggr(ChangeDate - Above(ChangeDate)+1, ProjectName, (ChangeDate, (NUMERIC)))

image.png

 

View solution in original post

sunny_talwar

Try this

Aggr(RangeSum(Above(ChangeDate - Aggr(Above(ChangeDate), ProjectName, (ChangeDate, (NUMERIC))), 0, RowNo())), ProjectName, Health, (ChangeDate, (NUMERIC)))

image.png

View solution in original post

6 Replies
sunny_talwar

May be something like this?

 

Aggr(ChangeDate - Above(ChangeDate)+1, ProjectName, (ChangeDate, (NUMERIC)))

image.png

 

dtate1959
Contributor III
Contributor III
Author

That works great , If I can prevail just  bit further, in the case where there is more than one column of states, how can I get the difference for each column?

 

ChangeDateProjectNameStateHealth
1/1/2019Test1ActiveGreen
1/1/2019Test2ActiveGreen
1/1/2019Test3ActiveGreen
1/6/2019Test1ActiveYellow
1/8/2019Test2ActiveYellow
1/10/2019Test1On HoldGreen
1/12/2019Test3On HoldYellow
1/12/2019Test2On HoldGreen
1/13/2019Test1On HoldRed
1/16/2019Test1On HoldGreen
1/16/2019Test2On HoldGreen
1/16/2019Test3On HoldRed
1/18/2019Test3ActiveGreen
1/20/2019Test2ActiveRed
1/17/2019Test1ActiveGreen
sunny_talwar

What would you like the output to look like?

dtate1959
Contributor III
Contributor III
Author

If possible like this, but, I am open to suggestions ?

ChangeDateProjectNameStateDays since ChangeHealthDays since change
1/1/2019Test1Active0Green0
1/6/2019Test1Active5Yellow5
1/10/2019Test1On Hold4Yellow9
1/13/2019Test1On Hold3Red3
1/16/2019Test1On Hold3Green3
1/17/2019Test1Active1Green

4

 

sunny_talwar

Try this

Aggr(RangeSum(Above(ChangeDate - Aggr(Above(ChangeDate), ProjectName, (ChangeDate, (NUMERIC))), 0, RowNo())), ProjectName, Health, (ChangeDate, (NUMERIC)))

image.png

dtate1959
Contributor III
Contributor III
Author

Thanks!!, that gives me what I need to flesh out the rest of my table!!