topic Re: Conditional colours for Scatter Chart dots above and Below the Line in QlikView

Conditional colours for Scatter Chart dots above and Below the Line

reanfadyl — Tue, 21 Jun 2016 02:33:41 GMT

Hi Folks,

I am wanting to colour the chart below as follows:

Above the Linear Line - Blue

Below the Linear Line - Red

Current Month - Green

Any suggestions on the formula to use in the Colour Definitions would be greatly appreciated.

Re: Conditional colours for Scatter Chart dots above and Below the Line

migueldelval — Tue, 21 Jun 2016 08:26:46 GMT

Hi Rean,

Try this:

(

"Expresion Llinear Line">''Value" and Month='month(today())',

rgb(10,245,10),

(

"Expresion Llinear Line">''Value",

rgb(80,160,240),

rgb(255,0,0)

)

Regards

Miguel del Valle

Re: Conditional colours for Scatter Chart dots above and Below the Line

michael_klix — Tue, 21 Jun 2016 11:55:45 GMT

Hi Rean,

try out this, it should work.

1. calculate formula for the reference line
2. use background colour of 1st dimension to define colour and insert formula (if everything fails define fixed axis).
3. for the reference line, I used just an overlay line on the bottom 😉

Formula for the colour in my case is

=if (
MonthStart(Date)=MonthStart(Today()),green(),
if(
GLAmount>=PatientDays*10,blue(),red()
)
)

If anybody tells me how to add an attachment here, I can post the qvw, too.

Keep Qliking

Michael

Re: Conditional colours for Scatter Chart dots above and Below the Line

reanfadyl — Wed, 22 Jun 2016 22:27:33 GMT

Thanks both, it was actually the method for calculating the reference line I was mainly after, which I think is uses the "Linest" function.

Re: Conditional colours for Scatter Chart dots above and Below the Line

michael_klix — Thu, 23 Jun 2016 11:54:38 GMT

Hi Rean,

as you seem to need a linear trendline and not just a diagonal line there is the problem that you have to display a line function inside a scatter chart.

To be honest, I have no clue how this should work, even with the reference line.

So the way out is a second line chart with the same axis, invisible apart from the line itself in the background.

1. calculate formula for the reference line via linest...()
2. use background color of 1st dimension to define color and insert formula
3. Fix axis to max +10%
4. for the reference line, create separate line chart with same fixed axis
5. put underneath and synchronize using "show grid".
6. when all fits, make all colors of the line chart transparent except for the trend line

-> note that the colors and trendline will react on selections, too

And this is the revised formula for the colours:

=if (
MonthStart(Date)=MonthStart(Today()),green(),
if(
GLAmount>=(PatientDays*vM)+vB,blue(),red()
)
)

while vM is linest_m() and vB is linest_b()

and that's how it looks:

and with a selction it changes e.g. to

Alternatively to avoid all this mess with the synchronized charts, you could as well use a line chart with just symbols instead. The problem with potentially not unique y-values can be solved by adding a really small random value to the x-values while loading.

e.g.

LOAD

PatientDays + (Rand()/100000) as PatientDays ,
GLAmount,
Date

...

Then you can simply add a linear trendline for your first expression only(GLAmount ) where you wont even need the linest()

or create any other calculated reference line as a second expression.

If you want the date to be shown too, just add another expression only(Date) and tick "text as popup".

Downside of this dirty little trick: I have no idea how to erase the delta for display. In my version no rounding or number formatting helps.

Hope it helped

Michael