changing line graph color based on Trendline

[sayadutt]

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

[lorenzoconforti]

Hi

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