How to filter by color (or the underlying value)?

[posywang]

Hi,

I'd like to see if there's a way to filter by color or underlying value.

The attached is a sample QVW. Basically, if user selects a color, let's say, Green, I'd like to filter out all other rows with Red or Yellow variances.

Definition of color: within 5% variance, green; within 10% variance, yellow; over 10% variance, red.

[vishsaggi]

Can you try this alternative?

Attached the file!

[posywang]

I love your attached example! It triggers my ideas of using some techniques. However, I am afraid that I don't know how to apply it to my original question. Maybe I didn't get how to use it? Can you please elaborate? Thanks!

[vishsaggi]

Which part you did not get. I used the expression to create a flag for these 3 different colors, then using 3 different buttons i am trying to select in the table based on color. Did you see the load script?

[vishsaggi]

Or try adding those 3 variance expressions in to variables and use those variables in those buttons like:

LET vRed    =  '(' & Concat(IF( (Budget-Actual)/Budget >=.10 OR (Budget-Actual)/Budget <= -.10 , Budget), '|') & ')';

LET vGreen =  '(' & Concat( If(  (Budget-Actual)/Budget < 0.05 and  (Budget-Actual)/Budget > -0.05 , Budget) , '|') & ')'

LET vYellow=  '(' & Concat( If(((Budget-Actual)/Budget > .05 and (Budget-Actual)/Budget < .1) OR ( (Budget- Actual)/Budget <= -.05 and (Budget-Actual)/Budget >= -.1),Budget), '|')  & ')'

And in those buttons Actions tab -> add select in Field -> Budget and in searchstring add like = vGreen .....

Do ctrl+Alt+V to open the variable overview in this example sheet.

[posywang]

This is great! I notice that if I need to change from Yellow to Red, I need to clear selection first, then click Red button. Is there a way to click Red button just once and switch from Yellow to Red? Many thanks!

[vishsaggi]

Sorry Xia, i am not sure if we might have to do this in the script level to get some flags as mentioned in my first sample. Or a macro needs to be written here. I am not pretty sure why they are unable to toggle between the buttons. I will look into it and get back if i find any.