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