Is it possible to define a color rule for data point 1 based on its value relative to data point 2?

bjavery87
bjavery87 Member
in Charting

I have 2 different lines with symbols on a graph. One line represents the target value and the other line represents the actual value. I want the symbol on the actual value line to be red when above the target value and blue when below the target value. I understand that I can change colors based on whether the actual value is above or below a manually entered value, but the target value changes every month, and I need the color of the actual value to automatically change when it dips below or rises above the dynamic target value.

Answers

  • ArborRose
    ArborRose Coach

    I would look into creating a custom column {formula} with the condition you need. The resulting column can then be used as the color condition. Thus, the new column would show when it dips below or rises above, and the color settings would react to that.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • McSQL
    McSQL Contributor

    @bjavery87 How I have done this in the past is almost identical to the suggestion from ArborRose, but here is more detail. I created two beast modes.

    beast mode 1:case when value>=(target value) then value else 0 end

    beast mode 2: case when value<(target value) then value else 0 end

    use a line stacked bar chart. set color of beast mode 1 to red, beast mode 2 to blue. The chart will stack the red and blue but you will only see one or the other because the result of one beast mode should always be 0.

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories