Linear trend line in Graph for Time Series Data

Q&A
, ,
1

Hi All,

I have a line chart now available in Quicksight associated to a time series data. Is there any way I can add/show a linear trend line to the chart, either linear or exponential? 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. WindowAvg function does not help in achieving a smooth line.

The data set is given below:
QuestionSample

The following community post talks about the calculation of y=bx+a but there is no way to calculate the b and a dynamically and programmatically. Linear trend line in Graph

1 Like
2

@pavanvlm ,

1/ Based on the previous example on calculating y=bx+a , are you able to get this calculation and does the trend value look correct ? > Once you can confirm this.
2/ What does calculating b and a dynamically mean > Can you add an example on the values you expect to see ?

Kind regards,
Koushik

1 Like
3

HI Koushik,

I was not able to create the y=bx+a calculation on quicksight. as there is no mechanism in quicksight to create a dynamic parameter value based on the calculation mentioned in the post.

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))

I am not able to create a new calculated field which satisfies this equation for me on the time series data attached.

4

Hi @pavanvlm,
It’s been awhile since last communication took place on this thread, are you still working on this scenario or were you able to find a work around in the interim?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

HI Brett,

I have not been able to find a work around for this issue. Still waiting for a solution which can be utilized for creating Trend lines on Time Series data.

Thank you

6

Hey @pavanvlm

What part of the calculation are you having the most trouble recreating and what parts need to be dynamic? Specifically I’m not sure what you mean here:

The workaround I see the most for this issue is creating the calculation in SQL before ingestion to QuickSight. Example:

7

Hey @pavanvlm

Are you still working on this or were you able to find a solution?

8

HI Duncan,

I was trying to setup the calculation on Quicksight itself. Will try the SQL mechanism before ingesting the data to Quicksight.

Hope it will work

1 Like