Color by average not matching line average

[Jeremy Visser]

Hi,

I am generating a histogram and would like to color my bars using standard deviations. I'm using the expressionAvg([Axis.Color])+1*StdDev([Axis.Color]) to create the color segments. The problem I seem to be having is that the average and standard deviations showing up in the color byis different the actual average of the data.

I inserted a vertical line showing my average and +/- 1 standard deviation and it doesn't match the numbers showing up in my legend.

Any help would be appreciated.

Thanks!

[Anders Gavare]

It looks like you are using just a continuous expression (Spacing in) without aggregation to color by. Although not obvious at first sight, this may result in a bar chart mode that may not be what you want (overlapping bars).

What I would do in a case like this is:

1. First, remove all coloring and make a note of the height of the bars (% of total rows, in your case), so that you know whatreasonable values are. The goal with the coloring here is just to add color, not to change the calculation.

2. I would try something like this (Custom Expression), which checks if the lowest data value (Min) in a bar is above some certain threshold (avg, avg+1, and so on). Typically, but not always, this would be something close to the leftmost border of the bar.

case

when Min([spacing in])>First(Avg([spacing in]) + (1.0 * StdDev([spacing in]))) OVER (All([Axis.X])) then "> Avg + 1"

when Min([spacing in])>First(Avg([spacing in])) OVER (All([Axis.X])) then "> Avg"

when Min([spacing in])>First(Avg([spacing in]) - (1.0 * StdDev([spacing in]))) OVER (All([Axis.X])) then "> Avg - 1"

when Min([spacing in])>First(Avg([spacing in]) - (2.0 * StdDev([spacing in]))) OVER (All([Axis.X])) then "> Avg - 2"

endThen use "Unique values" coloring on the resulting strings.

(The OVER expressions are a bit hard to read, but hopefully this should make it react correctly when filtering the data and so on.)

This should result in coloring of complete bars, while still preserving the correct Y values.