How to use multiple conditions on several fields combined logically in a set expression

[vvira1316]

Hi,

I've to achieve following in a set expression

Condition (C1): Field1 (F1) Starts with 'ABC'

Condition (C2): Field1 (F1) in ('Value1', 'Value2')

Condition (C3): Field2 (F2) in ('Value4', 'Value5')

Condition (C4): Field3 (F3) = False

Condition (C5): Field3 (F3) = True

Condition (C6): Field4 (F4)  in ('Value6', 'Value7')

These several conditions are combined in a following logical combination

Logical Expression(LE) /Combination of above conditions: (C1 or C2) and ((C3 and C4) or (C3 and C5 and C6))

eventually I've to use that in a set expression or have the set reduced with above Logical Expression(LE)

=count( {<LE>} ID)

Thanks,

Vijay

[Gysbert_Wassenaar · Accepted solution]

Try this:

=count( {<F1={'ABC*','Value1','Value2'},F2={'Value4','Value5'}>*(<F3={0}>+<F3={-1},F4={'Value6','Value7'}>)} ID)

[vvira1316]

Hi Gysbert,

Thanks for the quick response. Let me try and I'll update.

[vvira1316]

Hi Gysbert,

A small correction required in your expression otherwise it worked.

=count( {<F1={'ABC*','Value1','Value2'},F2={'Value4','Value5'}>*(<F3={0}>+<F3={1},F4={'Value6','Value7'}>)} ID)

it should be 1 and not -1 (for true)

My count is off by 1 (from reference number) probably because of data extract limitation rather than the formula. I'll check other similar set analysis and see if any of those numbers are matching.

Thanks,

Vijay

[Gysbert_Wassenaar]

1 or -1 depends on your source data. In Qlikview if you create an expression =NOT 0 you get -1 as a result. Actually Qlikview will treat any number that's not 0 as true.

But great to hear you got it to work!

[vvira1316]

Hi,

Thanks for the clarification. Can you please advise how I can implement following in a set analysis

record count where AgreementDate >= "01/01/2014"

I've created following variable to use in set analysis

vAgreementDate =Date#('01/01/2014','MM/DD/YYYY')

=Count({<[Agreement Date]={">=($vAgreementDate)"}>}[ID])

regards,

Vijay

[vvira1316]

I got it working

vAgreementDate = Floor(Date#('01/01/2014', 'MM/DD/YYYY'))

[Agreement Date]={'>=$(=vAgreementDate)'}