Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I collect the below data for multiple Clients, Project IDs, and Statuses. I want to be able to count the number of days that each project is in the same status. How do I do this with either a script or set analysis? The below data shows the statuses over time, but I only need to know how many days each project has been in the last status. In the below example calc I subtracted the dates 23-Apr-16 and 15-Apr-16 to get 18 days. I generated the below straight table using the Aggr function in the Expression.
Client | Project ID | Supplier | Status | Load Day | Last Status Days |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 23-Apr-16 | 18 |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 22-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 21-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 20-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 19-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 18-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Stakeholder | 15-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 14-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 13-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 12-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 11-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 08-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 07-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 06-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 05-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 04-Apr-16 | |
Client A | 12345 | Supplier A | With Customer - Legal | 02-Apr-16 | |
Hi Ed!
Try having a straight table with dimensions:
Client, Project ID, Supplier, Status
And then have an expression like:
Max([Load Day]) - Min([Load Day])
Hope this helps!
I'm assuming a project can only have 1 status per day?
Unless your data volume is huge, you shouldn't have to take this logic to the script. What are the dimensions of the chart that you want to display this expression in? In the above example, you included Status and Date as dimensions, but I'm assuming that wouldn't be what you'd actually want? Assuming your only dimensions would be Client, Project ID, and Supplier, your expression would look this like:
max(aggr(
if(Status=firstsortedvalue(total <[Project ID]> Status,-[Load Day]),
[Load Day]
)
,[Project ID],[Load Day]
))
-
min(aggr(
if(Status=firstsortedvalue(total <[Project ID]> Status,-[Load Day]),
[Load Day]
)
,[Project ID],[Load Day]
))
+ 1
Edit: this is also assuming each project can only have one Supplier. If that's not the case, simply add Supplier to both your aggr() dimensions and your firstsortedvalue() subtotal.
Regards,
Vlad