Calculate the duration between two stages on a bar chart

[kvr9]

Hi,

I got a requirement to calculate the duration taken place between two stages data is as below:

Load * inline[

Job id,emp name, state,time

1,A,Start,1/1/2018

1,A,Process ,2/1/2018

1,A, End,3/1/2018

2,C,Process 23/02/2019

2,C,Start 26/02/2019]

Country:

Load * inline[

Job id, Country

1,US

2,UK

3,UAE]

I have to tables and need to calcualate the duration between stages (process -end)against a emp name in a table and show the avg time taken to complete between process - end stage by country

Thanks in advance!!

]

[Saravanan_Desingh · Accepted solution]

Try this,

SET DateFormat='D/M/YYYY';

Transaction:
Load * inline[
Job id, emp name, state, time
1, A, Start, 1/1/2018
1, A, Process, 2/1/2018
1, A, End, 3/1/2018
2, C, Process, 23/02/2019
2, C, Start, 26/02/2019
];

Country:
Load * inline[
Job id, Country
1, US
2, UK
3, UAE
];

Left Join(Transaction)
LOAD [Job id],[emp name], time As Dt_Process
Resident Transaction
Where state='Process';

Left Join(Transaction)
LOAD [Job id],[emp name], (time - Dt_Process) As Duration
Resident Transaction
Where state='End';

Dimension:

Country

Expression:

Sum(Aggr(Duration,[Job id],Country))/Count(DISTINCT [Job id])