Color Rules for variable comparisons

elizab

I would like to be able to apply color rules on a value in comparison to another value, but such that both values can change based on filters.

We have a group of schools for which we have student outcome date in aggregate by school, year, and assessment window. We currently have a School Value card and a Network Average card. The School value card changes with the following filters: school, year, and assessment window. The Network Average card changes with just the year and window filters.

A viewer can see the comparison with the cards side-by-side, but I would also like the School value to change color when it is greater than (green) or less than (red) the Network average card.

I would like for the color to be applied dependent on the filter at the time.

ColemenWilson

You'll need to do some ETL work involving window functions. First, you will need to compute the average for each combination of year & assessment window. Here is how you would do it:

Step 1: Create an average grouped by year and assessment window

Step 2: Join the averages back to your data on Assessment Window and Year

Your output data will look like this:

Now you'll be able to compare the school(s) average to the MAX value of Window Function based on the assessment window/year selected. The school(s) average will change, but the assessment window/year will remain fixed.