topic Re: Help With If/And Statement in QlikView

Help With If/And Statement

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm not very familiar with writing if statements. I have fields A, B, and C. I would like field C to be yellow(vColorDBWarn) if field A is greater than 5 days old and if field B contains a value(which would be a date in this case).

In another area I only wanted field C highlighted if field A was greater than 5 days old and successfully wrote:

=if(Today()-[Field A]>=5,vColorDBWarn)

I would appreciate any help in changing that statement to add the condition of field B containing a value.

Thanks!

Dan

Re: Help With If/And Statement

sunny_talwar — Thu, 02 Nov 2017 17:50:02 GMT

May be this

If(Today() - FieldA >= 5 and (Len(Trim(FieldB)) > 0 or FieldB <> 0), vColorDBWarn)

Re: Help With If/And Statement

greend21 — Thu, 02 Nov 2017 18:02:45 GMT

I tried your initial formula and two variations

Neither of these worked:

=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt])>0 or [CapAdjCeded Inv Dt]<>0)),ColorDBWarn)

=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt])<>0)),ColorDBWarn)

This did work:

=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt])>0)),ColorDBWarn)

Thank you for the help!

Re: Help With If/And Statement

sunny_talwar — Thu, 02 Nov 2017 18:08:11 GMT

This would have worked had you not misplaced your parenthesis

=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt]))>0 or [CapAdjCeded Inv Dt]<>0)),ColorDBWarn)

Red is not needed and green was needed

Re: Help With If/And Statement

sunny_talwar — Thu, 02 Nov 2017 18:08:40 GMT

But I wonder how this worked.... because this still have parenthesis issue....

=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt])>0)),ColorDBWarn)

Re: Help With If/And Statement

greend21 — Thu, 02 Nov 2017 18:19:12 GMT

It looks like they all match up in this case.
=If(Today()-[CapAdjPaid Dt]>=5 and (Len(Trim([CapAdjCeded Inv Dt])>0)),ColorDBWarn)

Re: Help With If/And Statement

sunny_talwar — Thu, 02 Nov 2017 18:27:08 GMT

It should be more like this

=If(Today()-[CapAdjPaid Dt]>=5 and Len(Trim([CapAdjCeded Inv Dt]))>0,ColorDBWarn)