Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

AND operator in set analysis

Hi all,

     I have a pivot table as in the image below, but there's one exception only for AMS (row dim), when the role = 'Guarantee' (the role is not in the pivot), so I should remove those from the "QD" count. But how do I include that in the set analysis to say "where region<>'AMS' AND role<>'Gaurantee' " without loosing the whole AMS row?

=Count({<[Report Date]={"$(MaxDate)"}>} [StatusID])

pt.PNG.png

12 Replies
Champion
Champion

Hi Oscar,

you need no AND inside a set_expression. Just separate several set_expressions with a comma.

Creator III
Creator III

Hi DataNibbler,

That doesn't work because it will match/remove any coincidence, if for example I write this

=Count({<[Report Date]={"$(MaxDate)"},REGION-={"AMS"},ROLE-={"Guarantee"}>} [StatusID])


In the pivot table, I'll missing any AMS data and all data with "guarantee" role

Partner
Partner

You can use the Set Modifier P (possible), which in below example gives a list of possible id's that meet your criteria

=Count({<[Report Date]={"$(MaxDate)"},[Id]=P({1<Region={'<>AMS'},Role={'<>Guarantee'}>} [Id])>} [StatusID])

Partner
Partner

Do you have the Guarantee role only in the AMS region? Try this:

=Count({<[Report Date]={"$(MaxDate)"},role-={'Guarantee'}>} [StatusID])


If you have the role in other regions and you want to exclude it only for the AMS region then you might need to use an if in your expression:


if(region = AMS, Count({<[Report Date]={"$(MaxDate)"},role-={'Guarantee'}>} [StatusID]), Count({<[Report Date]={"$(MaxDate)"}>} [StatusID]))


or create a new field in your script:


load

..

if(region=AMS and role='Gaurantee', 0, 1) as flag_validRecord

from...


then in your expression:

Count({<[Report Date]={"$(MaxDate)"},flag_validRecord={1}>} [StatusID])

Partner
Partner

Hi,

maybe like this:



=Count({<[Report Date]={"$(MaxDate)"}, Region-={'AMS'}, Role-={'Guarantee'}>} [StatusID])

use -= to exclude values from set analysis

regards

Specialist
Specialist

Try this and confirm

=Count({<[Report Date]={"$(MaxDate)",Region-='AMS',Role-='Gaurantee'}>} [StatusID])

Creator III
Creator III

Happend what I thought, AMS QD value is 0, it should not

Capture.PNG.png

Creator III
Creator III

Thanks Piet but it didn't work, I tried E() instead of P() and I get a very similar result of what I'm looking for, but still is not the answer.

Creator III
Creator III

hi Gabriela, I just wanted to find a way to do it with set analysis