cancel
Showing results for
Did you mean:
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:

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:

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

]

1 Solution

Accepted Solutions

Try this,

``````SET DateFormat='D/M/YYYY';

Transaction:
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:
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])

Try this,

``````SET DateFormat='D/M/YYYY';

Transaction:
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:
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])