Calculate Duration of Actual Working with TIMESTAMP
Hello everyone,
I am currently using a metric to calculate the duration between 2 Timestamps :
Interval(Max([TIMESTMP])-Min([TIMESTMP]),'h:m'
If I select a specific day, my value is fine => I obtain the number of hours of working during the day selected.
MY PROBLEM is that I want to calculate the number of working hours over several days and if I select more than 1 day the metric that I use will include the time between last timestamp of today and first timestamp of tomorrow. As a consequence I obtain a much bigger duration that I want.
To clarify a bit, what I want is to be able to sum the different durations that I obtain for each separate days instead of measuring the time between the first timestamp of the first day and the last timestamp of the last day.
Illustration:
Transaction Number
Date
Hour
x1
1-AUG-15
8:00
x2
1-AUG-15
12:00
x3
1-AUG-15
16:00
x4
2-AUG-15
9:00
x5
2-AUG-15
12:00
x6
3-AUG-15
8:00
x7
3-AUG-15
14:00
For example in this case, the metric i'm currently using gives me the whole time between 8:00 on 1st august and 14:00 on 3rd august => 54 hours (between those 2 hours) WHAT I WANT is a formula that would give me : 8 (hours of 1st day) + 3 (2nd day) + 6 (3rd Day) = 17 hours of Actual working.
If you can help me I'd be really graceful. Have a good day !
