topic Re: Help with expression in QlikView

Help with expression

hopkinsc — Wed, 28 May 2014 11:29:16 GMT

Hi All,

I have a field 'TimeTaken' which holds the time taken to complete a job in the format of hh:mm:ss.

I want to sum another field (Completed_Flag) where the TimeTaken is less than 4 hrs.

Can anyone help please?

Re: Help with expression

tresB — Wed, 28 May 2014 11:32:59 GMT

Something like:

=If(Hour(Completed_Flag)<4, Completed_Flag+TimeTaken)

Re: Help with expression

its_anandrjs — Wed, 28 May 2014 11:37:02 GMT

Can you elaborate more about the fields details.

Re: Help with expression

hopkinsc — Wed, 28 May 2014 11:39:56 GMT

i get a null with that expression.

I thought it would be something like

Sum(Completed_Flag)

with some set analysis to say only look at the rows where TimeTaken is less than 4hrs

Re: Help with expression

tresB — Wed, 28 May 2014 11:44:29 GMT

How does the data look like in Completed_Flag field? Could you post a sample?

Re: Help with expression

hopkinsc — Wed, 28 May 2014 11:45:07 GMT

Hi Anand,

so the fields are as follows..

Completed_Flag which is either a 1 or 0

TimeTaken which is in the format of hh:mm:ss

So i want to sum the Completed_Flag where TimeTaken is less than 4hrs (04:00:00)

so if i have the following...

OrderNumber Completed_Flag TimeTaken

1000 1 02:14:25

1001 1 05:53:11

1002 0 01:10:07

I would only want OrderNumber 1000 and 1002 to be included in my result, the result would be 1 because 1002 has a 0 for the Flag.

Does that make sense?.

Re: Help with expression

jagannalla — Wed, 28 May 2014 11:48:26 GMT

Hi,

Take the dimension as =If(Hour(TimeTaken)<4,Order)

and expression as Sum(Completed_Flag)

Cheers!!

Jagan

Re: Help with expression

tresB — Wed, 28 May 2014 11:49:30 GMT

Try like:

=Sum(If(Hour(Time#(TimeTaken,'hh:mm:ss'))<4, Completed_Flag)

Re: Help with expression

its_anandrjs — Wed, 28 May 2014 11:50:46 GMT

Try with this

Sum(If( Hour(TimeTaken,'hh') < 4, Completed_Flag)

Re: Help with expression

its_anandrjs — Wed, 28 May 2014 11:53:53 GMT

Try with this expression

Sum(If( Hour(TimeTaken,'hh') < 4, Completed_Flag)

Re: Help with expression

hopkinsc — Wed, 28 May 2014 13:07:18 GMT

hey guys, i am having issues with this and have posted a sample here if you could help

What is wrong with this expression?