Linear trend line in Graph

Question & Answer
,
1

Hi All,

I have a line chart now available in Quicksight. Is there any way I can add/show a linear trend line to the chart? If the Line Chart doesn’t support is there any other chart visual which support this feature? If trend lines are not yet supported in Quicksight what will be an alternative. Does a WindowAvg function help in achieving a smooth line?

2

Hey
yes typically I use windowAvg to smooth out noisy time series graphs as follows

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

Result is the red graph below

image
image1578×568 128 KB

This would average over the last 7 data points. Increasing this will smoothen out the lines further.

1 Like
3

Thanks darcoli for helping out. Indeed I was trying out the windowAvg function. And your reply now assures me that I am going in the right direction. However, I have a hard time understanding the startindex and endindex params in windowAvg. I understood it’s for filling the missing points which is indeed I want too. Could you please share some examples/articles which will help me decide what to give for start and end index in the function. I couldn’t find any examples online.

See the below table where I have applied the windowsAvg with 2,0 as start and end index. It doesn’t look good. It seems like 3,2 will be fine. But I can’t come to a conclusion. How do we decide?

score
score601×502 23.7 KB

4

The start and end parameters in windowAvg denote the number of rows to consider before and after the current row for calculating the average. So with start=2 and end=0, you would be considering three rows for the average (the current row’s value and for the 2 preceding it).

So, for example, for the row for Feb5 you would be considering the values from Feb5, Feb 4 and Jan 30 to work out the average. In fact (60.05+ 56.85+46.67)/3 = 54.52 same as the WINDOWAVGS2D figure you got for that day.

The problem I see in your data is that you have days where score value is NULL. Depending on your use case, you could either filter these out completely by adding a Filter on the visual to exclude NULLS.
Another option is to use a calculated field to set score value to 0 instead when it is null: ifelse(isNull(score), 0, score) and then use this calculated field in the windowAvg calculation.

In addition you have days which are missing completely from the data. These can be problematic for the trendlines and you should consider changing the dataset in a way to make sure there is always a row for every date.

2 Likes
5

Thanks darcoli for taking your time and explaining everything. That detailed explanation with an example really helped a lot. Finally, I understood the logic behind the indexes.

Thanks for also pointing out the flaws in dataset.

6

It’s a bit of work but for anyone who really wants linear regression calculations in QuickSight here’s how it can be done:

y = bx +a

Where b = slope of the graph and a = y intercept.

Using an example where x’s represent months from 1 to n, and the y values are Revenue:

Create a calculated field for Slope (b):
(COUNT(Revenue) * SUM(Revenue * Month)-SUM(Revenue) * SUM(Month))/(COUNT(Revenue) * SUM(Month^2)-(SUM(Month)^2))

and a calculated field for Y Intercept (a):
AVERAGE(Revenue) - (Slope * AVERAGE(Month))

You could then calculate y’s for every x, and plot the result to create a trendline.