2 Replies Latest reply: Feb 6, 2013 4:38 AM by Jonathan Brough

# calculating time values and having null values.

I'm loading time values from an excel spreadsheet: however not all of the time values are in the xls sheet present at time of rebuild.

I would like to show in different tables/ charts the calculated (sum) values of two time values ; despite one that could be empty (null?)

=Sum(time1 + time2)

This will work when time1 and time2  both have a value, but would fail when one of them is empty.

=interval(time1 + time2) however would work but as this is a calculated value, this could not be shown in a straight table.

I'm looking for two solutions: one to be used in a chart

using my script :

 interval([time1] + time) AS Totaaltime,

does not seems to work.

what is the best practice dealing with null values?

• ###### Re: calculating time values and having null values.

No sure about the Chart expression, but for the script can you test if it is null and if so add zero instead ?

interval([time1] + if(len(time)>0,time,0)) AS Totaaltime,

I often have problems testing for nulls, with IsNull() function not returning what I expect. This is why I test length instead.

Jonathan

• ###### Re: calculating time values and having null values.

hiiiii

Interval(if(Len(Time(time1,'hh:mm:ss')),time1,0)+if(Len(time(time2,'hh:mm:ss')),time2,0))as total;

LOAD * INLINE [

time1, time2

8:00:12

6:30:12

, 4:09:8

, 5:12:09

12:00:00, 7:15:48

];

hope it helps