Forum Discussion

erasm_w's avatar
erasm_w
Frequent Visitor
6 years ago

DateDiff Function between changing variables

I am trying to calculate the time difference between conditions.

The workflow is as follows, there is a job created (Prim_ID_Fk) and everytime the job process changes it writes to a track table that adds the Prim_ID_FK as the job,  it adds a new process description and a time stamp when the job changed.

 

Is there a way to show per job the chagne from the old to the new job description and the time difference from when it was updated?

There is 11 Job processes:

Despatch, Prewash, Strip, Assesment, Quote, Go_Ahead, Wash, Assembly, Test, Spray, Despatch/Deliver

These description can exist in any combination

 

I would like to show:

 

Prim_ID_FK |  Process (Last) | Process (New) | Time |

1                    Despatch          Test                   02:10

1                    Test                   PreWash           01:15

1                    PreWash           Spray                00:25

2                    Despatch          Prewash            00:45

.......         

 

DIFF = 
    VAR Current_Job=LASTNONBLANK(aas_wf_track_tbl[Prim_ID_FK],[Prim_ID_FK])
    VAR Current_Date=MAX(aas_wf_track_tbl[Track_Date])
    VAR Current_State=LASTNONBLANK(aas_wf_track_tbl[Process_Description],[Process_Description])
    VAR Min_Date=SWITCH(Current_State,"Despatch", Current_Date,"Despatch",MAXX(FILTER(ALL(aas_wf_track_tbl),
        [Prim_ID_FK]=Current_Job&&[Track_Date]<Current_Date),[Track_Date]))
    VAR Max_Date=SWITCH(Current_State,"Despatch",MINX(FILTER(ALL(aas_wf_track_tbl),
        [Prim_ID_FK]=Current_Job&&[Track_Date]<Current_Date),[Track_Date]),"Test",Current_Date)
    RETURN
    DATEDIFF(Min_Date,Max_Date,MINUTE)

 

 

3 Replies