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.

ColemenWilson

The cards have no way to communicate with each other in that way, you will have to set color rules based on certain values that appear within that card. You can, however, have a filter applied to one card from another card and then have the filter effect color rules. Here is a KB article on dynamic color rules, use case #2 seems like it would solve for what you are trying to do:

https://domo-support.domo.com/s/article/360043428813?language=en_US#:~:text=You%20set%20color%20rules%20on,(if%20any)%20and%20color.

elizab

Thanks - I'm wondering if there's a way to use the Beast Mode Editor to create a comparison model with an output of 'above' / 'below', that could be used as the color rule condition. Is that possible?

elizab

ColemenWilson

Yes, if the data displayed on the 2 cards exists (or could be made to exist) within the same dataset then you could compare the values. Something like this:

CASE WHEN school value < network average THEN 'Below' ELSE 'Above' END

Then set the color rule based on above or below.

elizab

Yes, the two values are in the same dataset - in fact they are the same field/column ('On/Above'). It's just that the school filter doesn't apply to the Network Average card in the dashboard.

So in this instance would it be…

CASE WHEN 'On/Above'>AVG('On/Above) THEN 'above' ELSE 'Below' END

Except, because this beast mode output would be the color condition, I would need to pre-filter within the beastmode calculation to set the Network Average, so it won't filter by school, but the external filters can adjust it. Do you know how I might do that?

ColemenWilson

Could you provide some sample data? I am trying to better understand the values you are working with and how your data is structured. For example is 'On/Above' a text value or a number?

elizab

Sure - sorry.

'On/Above' is a number value that represents the percent of students in a school that were performing on or above grade-level at the time of the assessment window.

We have three assessment windows every year, so there are 'On/Above' values for every assessment window within every year for each school. So, in essence, there is an 'On/Above' number value for School A-Window 19-20. (e.g., .35)

School A (SY22-23)

Window 1=.30

Window 2=.35

Window 3=.40

The School average card defaults to aggregating all of those values, but our user has to select the filter criteria to get the specific average for that school, year, and assessment window.

The network average cards does exactly the same thing except only the year and assessment window filters can interact with it.

What I would like to have happen is that when I select the filter criteria for School A, SY22-23, Window 2, then I would see 35% on the School Average card, and I might see 37% on the Network average card. Then, the 35% would be red.

Does that help any?

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.