"And" function

[Breezy]

I do not have much experience with the 'and' function.

Here should be a correct version if the user chooses only Country  number 1. The table changes row colors based on if Country number 1 is selected.

=if([Country]='1', if(Even(RowNO(total))=0, rgb(152,251,152),rgb(144,238,144)),

if(Even(RowNO(total))=0, rgb(224,255,255),rgb(175,238,238)))

HOWEVER, now I want to change it so that if the user selects ALL of the countries at once (from the drop-down menu) the color function will still work. I clearly have a problem with the 'and' function. 

=if((Country= ’1’ and Country= ’2’ and Country= ’3’ and Country= ’4’ and Country = ’5’ and Country= ’6’ and Country= ’7’ and Country= ’8’ and Country= ’9’ and Country= ’10’, if(Even(RowNO(total))=0, rgb(152,251,152),rgb(0,255,127)),

=if((Country=’1’ and Country=’2’ and Country=’3’ and Country=’4’ and Country =’5’ and Country=’6’ and Country=’7’ and Country=’8’ and Country=’9’ and Country=’10’, if(Even(RowNO(total))=0, rgb(224,255,255),rgb(175,238,238)))

Please assist. Thank you.

[Or]

Indeed you have an issue - country = 1 and country = 2 will always be false, since it can't be both. In your case, when Country has multiple values selected, all of these will be false since Country is not equal to any single value.

Instead, you might try:

If(GetNotSelectedCount(Country)>0,Somecolor,SomeOtherColor)

Note that this forces the user to select all the countries, and it will behave differently if the user hasn't selected anything (which typically behaves the same way as selecting all of them), though.

Another option is to use GetFieldSelections() and combine that with the Match() function, e.g.

If(Wildmatch(GetFieldSelections(Country,',',12),'*Country1*','*Country2*'),SomeColor, SomeOtherColor)

Note that the 12 allows up to 12 values to be returned, if your list of countries is longer you'll need to increase it.

[Breezy]

Thank you.

How would I maintain my colors though? I have one table, that, depending on if all values are selected or not, have different alternating colors (such as: all values selected (or if zero values selected) is alternating light blue and dark blue and any other value combination = red and light red). I'm not sure how to incorporate my 'even(row)...' part into your code.

I see you wrote:  If(GetNotSelectedCount(Country)>0,Somecolor,SomeOtherColor)

My original had the alternating rows like this:

=if([Country]='1', if(Even(RowNO(total))=0, rgb(152,251,152),rgb(144,238,144)),

if(Even(RowNO(total))=0, rgb(224,255,255),rgb(175,238,238)))

So i am trying a combination of yours and mine:

If(GetNotSelectedCount(Country)>0,rgb(152,251,152),rgb(0,255,127)),

if(Even(RowNO(total))=0, rgb(224,255,255),rgb(175,238,238)))

That will not work though....

[Or]

Something along the lines of:

If(Even(RowNo(total)),if(GetNotSelectedCount(Country)>0,StripeColor,OtherStripeColor),if(GetNotSelectedCount(Country)>0,NonStripeColor,OtherNonStripeColor)))

Should probably work? Likely this can be cleaned up so the condition isn't repeated but I figured answering now would be more helpful than if/when I have a chance to sit down and refactor it.

[Breezy]

Hmmm, something is wrong with the condition there. I have four colors but regardless of whether I select all "Country", no "Country" or any individual (or one+two, etc.) the stripes stay the same. And only two colors appear (as opposed to four).

Here is what I did. I took the formula you recommended:

If(Even(RowNo(total)),if(GetNotSelectedCount('Country')>0,rgb(152,251,152),rgb(139,69,19)),if(GetNotSelectedCount('Country')>0,rgb(51,51,255),rgb(255,0,0)))

The colors that appear in my zebra stripes are:

brown (139,69,19)

red (255,0,0)

I should have all four colors (depending what I select determines which zebra pattern occurs)

[Or]

Remove the quotes from 'Country'. You're referring to a field name, and using quotes tells Qlik it's actually a string, not a field.

[Breezy]

I took the quotes away from Country. It's still giving me only two zebra stripes instead of four though.

If(Even(RowNo(total)),if(GetNotSelectedCount([Country])>0,rgb(152,251,152),rgb(139,69,19)),if(GetNotSelectedCount([Country])>0,rgb(51,51,255),rgb(255,0,0)))

[Or]

I'm afraid this is as far as I can go without actually having access to what it is you're working with. There's probably a logical error in there somewhere but I can't see it just reading the formula.

What I suggest you do is break up the formula into chunks and place them in the table, until you find out which part isn't working correctly and why, e.g. create measures such as

If(Even(RowNo(total)),1,0) and see if that part works as expected

GetNotSelectedCount(Country) and see if that works as expected

etc.

Doing this typically isolates the problem.

[Breezy]

I will do that! Thank you for your time and advice. Cheers!