Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Alexandre BERNARD
Try Interval(sum(aggr( Max([TIMESTMP])-Min([TIMESTMP]),Date)) ,'h:m' )
Interval(sum(aggr( Max([TIMESTMP])-Min([TIMESTMP]),Date,[Transaction Number])) ,'h:m' )