The time duration between multiple timestamps.

[PIOTR_GRABOWSKI]

Good day everyone

I'm new to the Qlik, and looking for help. I would like to get the time duration for the processing time based on the following table:

CONTRACT ID	EVENT TYPE NAME	PROCESSING USER	PROCESSING DATE	PROCESSING TIME	ACTION TYPE
ID 1	A	User 1	26.04.2023	12:50:13	Type 1
ID 1	B	User 1	26.04.2023	12:51:50	Type 2
ID 1	B	User 2	26.04.2023	12:51:53	Type 2
ID 1	A	User 2	26.04.2023	12:51:58	Type 3
ID 2	A	User 1	26.04.2023	12:51:58	Type 1
ID 2	A	User 2	27.04.2023	09:28:25	Type 2
ID 2	B	User 2	27.04.2023	09:28:50	Type 2
ID 3	A	User 3	27.04.2023	09:33:47	Type 1

I need to calculate how much time took each CONTRACD ID posistion case based on the PROCESSING TIME and to be able to later look into PROCESSING USER, EVENT TYPE NAME and ACTION TYPE details. So to know:

- how many minutes took ID 1 in total;

- how many minutes of this time is for User 1, how many for User 2;

- how much time took each EVENT TYPE NAME or ACTION TYPE in this ID.

Thank you in advance

[Digvijay_Singh]

May be try like this - 

data:
Load *,
interval(Time#([PROCESSING TIME],'hh:mm:ss'),'mm') as [PROCESSING TIME FORMATTED],
Date#([PROCESSING DATE],'DD.MM.YYYY') as [PROCESSING DATE FORMATTED]
;
Load * inline [
CONTRACT ID, EVENT TYPE NAME, PROCESSING USER, PROCESSING DATE, PROCESSING TIME, ACTION TYPE
ID 1, A, User 1, 26.04.2023, 12:50:13, Type 1
ID 1, B, User 1, 26.04.2023, 12:51:50, Type 2
ID 1, B, User 2, 26.04.2023, 12:51:53, Type 2
ID 1, A, User 2, 26.04.2023, 12:51:58, Type 3
ID 2, A, User 1, 26.04.2023, 12:51:58, Type 1
ID 2, A, User 2, 27.04.2023, 09:28:25, Type 2
ID 2, B, User 2, 27.04.2023, 09:28:50, Type 2
ID 3, A, User 3, 27.04.2023, 09:33:47, Type 1
];

1. CONTRACT ID Total TIME in MIN

interval(Sum(Aggr(nodistinct Sum([PROCESSING TIME FORMATTED]),[CONTRACT ID])),'mm')

2. PROCESSING USER Total TIME in MIN

interval(Sum(Aggr(nodistinct Sum([PROCESSING TIME FORMATTED]),[PROCESSING USER])),'mm')

3. EVENT TYPE NAME Total TIME in MIN

interval(Sum(Aggr(nodistinct Sum([PROCESSING TIME FORMATTED]),[EVENT TYPE NAME])),'mm')

4. PROCESSING USER Total TIME in MIN

interval(Sum(Aggr(nodistinct Sum([PROCESSING TIME FORMATTED]),[ACTION TYPE])),'mm')

[PIOTR_GRABOWSKI]

I did not give the correct information. The PROCESSING TIME is the hour on the clock of initiation of the EVENT TYPE NAME. So if for ID 1 we have:

CONTRACT ID	EVENT TYPE NAME	PROCESSING USER	PROCESSING DATE	PROCESSING TIME	ACTION TYPE
ID 1	A	User 1	26.04.2023	12:50:13	Type 1
ID 1	B	User 1	26.04.2023	12:51:50	Type 2

it reads as:
- User 1 started in ID 1 EVENT A at 12:50:13 on the clock

then

- User 1 started in ID 1 EVENT B at 12:51:50 on the clock - which ends EVENT A

So the total time for EVENT A is 12:51:50 - 12:50:13  - 1 minute 37 seconds.

It is expected that each ID should have a count from the first to the last event (hence date+time), and then separate each ID into the other information: user, event, action.

I apologise for the incorrect explanation in first data.