Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
]
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])
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])