Calculate the duration between two stages on a bar chart

kvr9 — Fri, 29 Oct 2021 17:07:26 GMT

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

]

Re: Calculate the duration between two stages on a bar chart

Saravanan_Desingh — Sat, 30 Oct 2021 15:06:25 GMT

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

topic Calculate the duration between two stages on a bar chart in App Development

Calculate the duration between two stages on a bar chart

Re: Calculate the duration between two stages on a bar chart