topic Re: If()Sum Expression Syntax Problem in QlikView

If()Sum Expression Syntax Problem

Anonymous — Sat, 03 Nov 2018 18:01:45 GMT

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

Re: If()Sum Expression Syntax Problem

andrey_krylov — Sat, 03 Nov 2018 19:55:41 GMT

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

Sum(fabs(ToTime - FromTime))

Re: If()Sum Expression Syntax Problem

Anonymous — Sat, 03 Nov 2018 20:14:59 GMT

Hi Andrey:

Works perfectly. Thanks very much.

Re: If()Sum Expression Syntax Problem

mikevwang — Sat, 03 Nov 2018 20:29:56 GMT

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

Re: If()Sum Expression Syntax Problem

andrey_krylov — Sat, 03 Nov 2018 20:33:49 GMT

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...))

Re: If()Sum Expression Syntax Problem

Anonymous — Sat, 03 Nov 2018 20:53:20 GMT

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