I am looking to create a line chart that visualizes all Jobs within a time horizon that are/were late while considering the multiple days they were late for. This breaks into two groups: jobs that were late and our now completed and jobs that are late and are yet to be completed. My aggregated data looks something like the following:
Consider Today=3/13/2020
| Jobkey | StartDate | DueDate | CompletionDate | Flag (1=late) |
| 6462 | 3/06/2020 3:50.4 | 3/13/2020 | - | 0 |
| 2490 | 3/05/2020 5:50.1 | 3/12/2020 | - | 1 |
| 3519 | 3/03/2020 13:50 | 3/11/2020 | 3/13/2020 | 1 |
so in a line chart I would want the above data would look like the following but am not having any luck.
| Time Dimension | Count of Late Jobs |
| 3/11/2020 | 0 |
| 3/12/2020 | 1 |
| 3/13/2020 | 2 |
| 3/14/2020 | 1 |
My fields are being calculated as follows:
- StartDate: aggr(min(TimeKey),JobKey)
- DueDate: lastworkdate(aggr(min(TimeKey),JobKey),6)+1
- Completion Date: aggr( max({<From={ready_to_ship},To={complete}>}TimeKey),JobKey)
- late flag:
sum(
//if condition for not complete jobs
if( Match(To,'ready_to_print','printing','ready_to_sheet','sheeting','ready_to_bind','binding') and MostRecentStatusFlag=1
and today()>lastworkdate(aggr( min(TimeKey),JobKey),6)
,1,
//If condition for complete jobs
if( Match(To,'complete') and Match(From,'ready_to_ship') and MostRecentStatusFlag=1
and aggr( max(TimeKey),JobKey)>lastworkdate(aggr( min(TimeKey),JobKey),6)
,1,0))
)
So the issue I am having is that I am able to identify a late job rather painlessly but I am not able to count that job it is late over the amount of days it is late for on a time dimension. My timekey for the jobs only come in when an event happens but I do have a master Time Calendar that I have been using to try and make this work.
Any suggestions are appreciated.
