Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

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

Hello!

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

ID_ENTRYTIME1TIME2TIME3
1-03:05:3500:00:18
2

00:05:07

-03:00:45
300:00:1500:07:53-
400:10:2800:35:1900:05:08
500:00:2505:00:1500:17:36
600: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!!!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

The sum of TIME1 could be calculated like

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

Sums for TIME2 and TIME3 accordingly.

View solution in original post

4 Replies
swuehl
MVP
MVP

The sum of TIME1 could be calculated like

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

Sums for TIME2 and TIME3 accordingly.

maxgro
MVP
MVP

see attachment

microwin88x
Creator III
Creator III
Author

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
MVP
MVP

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.