Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ChangeDate | ProjectName | State |
1/1/2019 | Test1 | Active |
1/1/2019 | Test2 | Active |
1/1/2019 | Test3 | Active |
1/6/2019 | Test1 | On Hold |
1/8/2019 | Test2 | Active |
1/10/2019 | Test1 | Active |
1/12/2019 | Test3 | On Hold |
1/12/2019 | Test2 | On Hold |
1/13/2019 | Test1 | On Hold |
1/16/2019 | Test1 | Active |
1/16/2019 | Test2 | On Hold |
1/16/2019 | Test3 | On Hold |
1/18/2019 | Test3 | Active |
1/20/2019 | Test2 | Active |
1/17/2019 | Test1 | Closed |
I am relatively new to using Aggr and Rank functions, so, any help is appreciated.
Thanks,
DT
May be something like this?
Aggr(ChangeDate - Above(ChangeDate)+1, ProjectName, (ChangeDate, (NUMERIC)))
Try this
Aggr(RangeSum(Above(ChangeDate - Aggr(Above(ChangeDate), ProjectName, (ChangeDate, (NUMERIC))), 0, RowNo())), ProjectName, Health, (ChangeDate, (NUMERIC)))
May be something like this?
Aggr(ChangeDate - Above(ChangeDate)+1, ProjectName, (ChangeDate, (NUMERIC)))
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?
ChangeDate | ProjectName | State | Health |
1/1/2019 | Test1 | Active | Green |
1/1/2019 | Test2 | Active | Green |
1/1/2019 | Test3 | Active | Green |
1/6/2019 | Test1 | Active | Yellow |
1/8/2019 | Test2 | Active | Yellow |
1/10/2019 | Test1 | On Hold | Green |
1/12/2019 | Test3 | On Hold | Yellow |
1/12/2019 | Test2 | On Hold | Green |
1/13/2019 | Test1 | On Hold | Red |
1/16/2019 | Test1 | On Hold | Green |
1/16/2019 | Test2 | On Hold | Green |
1/16/2019 | Test3 | On Hold | Red |
1/18/2019 | Test3 | Active | Green |
1/20/2019 | Test2 | Active | Red |
1/17/2019 | Test1 | Active | Green |
What would you like the output to look like?
If possible like this, but, I am open to suggestions ?
ChangeDate | ProjectName | State | Days since Change | Health | Days since change |
1/1/2019 | Test1 | Active | 0 | Green | 0 |
1/6/2019 | Test1 | Active | 5 | Yellow | 5 |
1/10/2019 | Test1 | On Hold | 4 | Yellow | 9 |
1/13/2019 | Test1 | On Hold | 3 | Red | 3 |
1/16/2019 | Test1 | On Hold | 3 | Green | 3 |
1/17/2019 | Test1 | Active | 1 | Green | 4
|
Try this
Aggr(RangeSum(Above(ChangeDate - Aggr(Above(ChangeDate), ProjectName, (ChangeDate, (NUMERIC))), 0, RowNo())), ProjectName, Health, (ChangeDate, (NUMERIC)))
Thanks!!, that gives me what I need to flesh out the rest of my table!!