Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vvira1316
Specialist II
Specialist II

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

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try this:

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

Try this:

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


talk is cheap, supply exceeds demand
vvira1316
Specialist II
Specialist II
Author

Hi Gysbert,

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

vvira1316
Specialist II
Specialist II
Author

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!


talk is cheap, supply exceeds demand
vvira1316
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

I got it working

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

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