changing line graph color based on Trendline

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have a simple line graph which spans over a period of time.

I have also a 'Linear Trendline' as shown below.

My requirement is to change the original graph line color (currently in blue), based on the Trendline.

So if the Trend is upwards, it should show 'red'. If the Trend is downwards, it should show 'green'.

Can you please help here.

Thanks

Re: changing line graph color based on Trendline

lorenzoconforti — Wed, 07 Feb 2018 17:11:50 GMT

First of all you need to calculate the slope of the trend line; you'll need to recalculate it as, depending on its value, you'll then be able to change the colour of the line

For a linear regression function, the slope is calculated using the LINEST_M function. See example below

=LINEST_M(total aggr(Sum (Sales),Month), Month)

A couple of important points:

- replace Sales and Month with your dimensions

- make sure you use the total qualifier as you will need to know the overall slope of the curve for every value of your month dimension

- I'm making the assumption that your data is already ordered by month; if not, in the aggregation you'll need to sort the data (using StructuredParameter syntax)

To change the colour of the line, edit the background colour of the expression with something like:

=if (LINEST_M(total aggr(Sum (Sales),Month), Month) >=0, green(), red())

Please see example attached; as you select between the two products the colour of the line will change according to the slope of the trendline

Lorenzo

topic Re: changing line graph color based on Trendline in QlikView

changing line graph color based on Trendline

Re: changing line graph color based on Trendline