Sum in a Chart Depending on 3 Fields which are not null

[microwin88x]

Hello!

I have a table in my model with the following columns:

ID_ENTRY	TIME1	TIME2	TIME3
1	-	03:05:35	00:00:18
2	00:05:07	-	03:00:45
3	00:00:15	00:07:53	-
4	00:10:28	00:35:19	00:05:08
5	00:00:25	05:00:15	00:17:36
6	00:03:50	-	-
7	-	-	-

Where TIME1, TIME2 & TIME3 have the format hh:mm:ss

What I need is to show in a chart (waterfall) the SUM of TIME1, TIME2 & TIME3 where all the times are not null AT THE SAME TIME.

In my example, I'd need to show the SUM of times for ID_ENTRY 4 & 5.

TIME1: 00:10:28 + 00:00:25 = 00:10:53

TIME2: 00:35:19 + 05:00:15 = 05:35:34

TIME3: 00:05:08 + 00:17:36 = 00:22:44

How could I do that?

Thank you!!!

[swuehl · Accepted solution]

The sum of TIME1 could be calculated like

=interval(sum(if(isnum(TIME2) and isnum(TIME3),TIME1)))

Sums for TIME2 and TIME3 accordingly.

[maxgro]

see attachment

[microwin88x]

Thanks. I liked your solution! And how could I do if I need to make a division between that sum and the count of times that there are sums for TIME1, TIME2 & TIME3 with no nulls? In this case would be:

=interval(sum(if(isnum(TIME2) and isnum(TIME3),TIME1)))

/

2 (I need an expression here to return me 2, that it's the count for those times with no nulls in the same moment)

--

Should this work? Or you find a better solution? For TIME1:

=interval(sum(if(isnum(TIME2) and isnum(TIME3),TIME1)))

/

count(if(isnum(TIME1) and isnum(TIME2) and isnum(TIME3),ID_ENTRY))

[swuehl]

This should work, just give it a try.

You also can flag your records in the script, so you don't need to do these lengthy checks in the frontend.