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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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