If()Sum Expression Syntax Problem

[Anonymous]

I have [ShiftTime] fields coming in for example as the following text fields:

18:00 - 03:00

08:30 - 12:00

13:30 - 16:00

22:00 - 01:30

where the text field represents a "From" to a "To Time".  I break these fields apart to obtain separated "From" and "To" values in the script using the following:

Subfield((ShiftTime),' - ',1) as FromTime,

Subfield((ShiftTime),' - ',-1) as ToTime,

With a 24Hour clock, I have the following Expression to calculate the "ShiftLength" by subtracting the "FromTime" from the "ToTime". 

=if(Sum(ToTime - FromTime)<0,(Sum(ToTime - FromTime)* -1),Sum(ToTime - FromTime))

Where the calculated "ShiftLength" is a negative value, as would be the case in 22:00 - 01:30, the negative value is multiplied by [-1].  This works on a line by line basis .  But now, I also want to get the total value of all "ShiftLength" values and cannot get the correct syntax as in:

               sum(if(Sum(ToTime - FromTime)<0,(Sum(ToTime - FromTime)* -1),Sum(ToTime - FromTime))

The preceding line gives me a syntax error.  And, I am never sure if it should be a Sum(If) or an If(Sum)....................

Any help would be much appreciated.

Thanks

Dave

[andrey_krylov]

Hi David. I think you can replace this long If() statement with a shorter fabs()  which returns the absolute value

Sum(fabs(ToTime - FromTime))

[Anonymous]

Hi Andrey:

Works perfectly.  Thanks very much. 

[mikevwang]

Did you intend to have '22:00 - 01:30' get a shift length of 20:30?

[andrey_krylov]

You are welcome. And the error is caused by sum(if(Sum. To solve it the first summation should  be aggregated

sum(Aggr(if(Sum(ToTime - FromTime)<0,(Sum(ToTime - FromTime)* -1),Sum(ToTime - FromTime), Dim1, Dim2...))

[Anonymous]

Hi Andrey

And thanks again. This problem has been bugging since 10:00AM this morning.  The 'absolute value' solution is best as you have mentioned - it is so simple!

Regards

Dave