topic Re: IF to exclude values in dimension/table in QlikView

IF to exclude values in dimension/table

Thu, 10 Dec 2015 15:30:01 GMT

I am trying to make a pivot table that shows Names according to the below criteria:

Active = Y (so do not include N values)

Role = A or B (so do not include C values)

Type = N or V (so do not include E values)

The below if statement is what I used in the dimension but I still get values returning that include E values:

=if(Active='Y' and Role='A' or Role='B' and Type='N' or Type='V', Name)

Please can anyone assist?

Thanks!

Re: IF to exclude values in dimension/table

sunny_talwar — Thu, 10 Dec 2015 15:35:30 GMT

Try this with parenthesis:

=If(Active='Y' and (Role='A' or Role='B') and (Type='N' or Type='V'), Name)

Re: IF to exclude values in dimension/table

Thu, 10 Dec 2015 16:53:51 GMT

Hi Sunny, unfortunately it didn't work. Are ther any other ways to do this?

Re: IF to exclude values in dimension/table

sunny_talwar — Thu, 10 Dec 2015 17:37:49 GMT

Not sure why it won't work, would you be able to share a sample?

Re: IF to exclude values in dimension/table

Kushal_Chawda — Thu, 10 Dec 2015 17:49:19 GMT

=if( Active='Y' and match(Role,'A','B') and match(Type,'N','V') , Name)

Re: IF to exclude values in dimension/table

Fri, 11 Dec 2015 08:53:26 GMT

I was doing it wrong!

I have another question. How do I do a count distinct in set analysis Im trying:

=count({<Active={'Y'}, Role={'A','B'}>}NoOfDaysOver30)

But I want to count distinct on name so it groups all the names together and gives me a score of 1 per name over 30 but this would also need to be based on latest date I believe