How To Calculate A Linear Trend Line

Question & Answer
, ,
1

Is there a better example with less vague details on how to better connect the dots and generate a linear trendline?

I am using a basic adventure works 2020 dataset with:

SalesAmount
OrderDate

and I am having a hard time putting the pieces together from this post on how to generate a linear trendline with basic data.

2

Neither this:

windowAvg(
sum({metric}),
[datetime ASC], 7,0,
)

Nor this:

(COUNT(Revenue) * SUM(Revenue * Month)-SUM(Revenue) * SUM(Month))/(COUNT(Revenue) * SUM(Month^2)-(SUM(Month)^2))

Works?

3

Thanks for the response @Max! Below are my attempts at using those calc logics. Am I doing something wrong?

image
image1425×759 168 KB

4

Can you make sure that on your slope line test, that the month number is the number from the start. i.e if you start your graph in July 2016, the first point should be 1, not necessarily based off of the month’s number (i.e July is the 7th month).

Secondly did you apply the slope to the y-axis graph?

AVERAGE(Revenue) - (Slope * AVERAGE(Month))

This is what you will add to your y-axis ^

5

Max,

Thanks for the responses.

  1. I filtered the graph to start at beginning MonthNumberOfYear to start at 1, and increment by 1 naturally with filters, which should allow us to move forward with the functionality testing piece.

I can look to make this more dynamic later, with possibly a Rank function or something.

  1. By Default, the Y-Axis on my line chart is based on the ‘Values’ I drag/drop into the field well, so I applied both of the Test measures and neither one is yielding a non-blank value for me in this context.

image
image1762×812 107 KB

Feels like we are getting closer… any additional direction/thoughts are much appreciated.

6

Hi @kwarren35 I’m facing the same problem. Did you find any way to create a straight line trend?
Thank you

7

I did not in quicksight. We got stuck so we pivoted to creating an aggregated view in SQL that manufactured the trendline.

Good luck!

1 Like