Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

If()Sum Expression Syntax Problem

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


5 Replies
andrey_krylov
Specialist
Specialist

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
Not applicable
Author

Hi Andrey:

Works perfectly.  Thanks very much. 

mikevwang
Contributor III
Contributor III

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

andrey_krylov
Specialist
Specialist

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
Not applicable
Author

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