topic Re: "And" function in App Development

"And" function

Breezy — Mon, 20 Dec 2021 14:20:25 GMT

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.

Re: "And" function

Or — Mon, 20 Dec 2021 14:45:07 GMT

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.

Re: "And" function

Breezy — Mon, 20 Dec 2021 18:47:10 GMT

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....

Re: "And" function

Or — Mon, 20 Dec 2021 20:10:18 GMT

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.

Re: "And" function

Breezy — Mon, 20 Dec 2021 23:33:21 GMT

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)

Re: "And" function

Or — Tue, 21 Dec 2021 10:10:05 GMT

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.

Re: "And" function

Breezy — Wed, 22 Dec 2021 21:05:18 GMT

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)))

Re: "And" function

Or — Thu, 23 Dec 2021 12:20:45 GMT

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.

Re: "And" function

Breezy — Thu, 23 Dec 2021 13:11:26 GMT

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