Calculating sum of duration for values that vary (not chronological)

[MATC]

Hello,

I've encountered an issue where I need to calculate sum of durration (e.g. for a day) for specific ID's (user can select whatever they want) 

On given table I have 2 values that are occupying the same time slot and sum of their duration is calculated to be 51 sec but in fact they are occupying only 31 sec. Is there a way to correctly calculate this on the front end (as user can select multiple different distinct ID's)
Here's another example of given selected data 

Correct duration is 5:11:33

[rubenmarin]

Maybe you can try a specific expression for total checking when dimensionality()=0, like:

If(Dimensionality()=0
  ,Sum(Aggr(Max(end)-Min(start),Date,id))
  ,end-start)

[MATC]

Unfortunately it doesn't seem to be working. Between max and min values there can be also values that start and end in that time span.

[berndjaegle]

In the script to load you need this field:
Load
YourField,
interval(YourDurationField, 'hh:mm:ss') as DurationInSeconds
From YourDataSource;

Then, you can calculate the sum of durations using the Sum function:

Sum(DurationInSeconds) as TotalDuration

[rubenmarin]

Hi, I'm not sure of the specific conditions of the calculations you have to do, maybe you only need to exclude 'id' from the Aggr() in my previous example

[MATC]

Hello,

For specific items this formula seems to be working but for example below it doesn't. on the right 1 is with ID in aggr 2 is without ID in aggr ( If(Dimensionality()=0
,Sum(Aggr(Max(end)-Min(start),Date,id))

It can't be more than 24h since it is min is 6am and max is 6am next day. Also it can't be full 24h since after 6:06 am there are no values to all the way to 9am
,end-start)

[rubenmarin]

Hi, the table doesn't shows the Date field, probably the row that starts at 2024/02/12 22:00 and ends at 2024/02/13 6:00 has 2024/02/12 as DAte, also the first row, so the difference between them is 24 hours.

[MATC]

Hey,

Yes, but for my needs I need to calculate the real duration and as you can see the real duration is not 24h because there are no usage between 6:06:01 and 8:52:36 and i need to be able to reflect that.

For the w/ID version this works if all the starts and ends are distinct but if selection has multiple of the same it is summing it up but it shouldn't and the w/o ID version it is summing the min-max without looking for whats inbetween which is understandable as we are not aggr by ID.

[Dataintellinalytics]

Please try TOTAL function to ignore Dimension.

Interval(Max(Total End) - Min(Total Start ) ,'hh:mm:ss').

Also for the given example I believe the correct duration should be  05:43:24 NOT  5:11:33.

 

[MATC]

This is the same case as above.

Duration is 5:11:33. as if you look closely there is a gap between 8:06:05 which is the end for the first 2 rows and 08:37:56 which is the start of the row 3. I have calculated the total duration (which would be 05:43:24  (06:36:13-12:19:37 - not including the 31:51 break inbetween)   but i need to reflect that in me calc.