Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

Calculate the duration between two stages on a bar chart

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!!

 

]

1 Solution

Accepted Solutions
Saravanan_Desingh

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';

commQV20.PNG

Dimension:

Country

 

Expression:

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

View solution in original post

1 Reply
Saravanan_Desingh

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';

commQV20.PNG

Dimension:

Country

 

Expression:

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