Skip to Content
Author's profile photo Former Member

Linear & Polynomial Trend Lines in Webi

Currently, there is no option to draw a linear or polynomial trend line in a webi chart. However, we can use mathematical calculations to overcome the challenge.

In this post, I utilize eFashion Universe for demonstration purposes. I am assuming that you are somewhat familiar with regression analysis and Webi 4.0 – Rich Internet Application Viewing Mode.

Warm-up reminders:

A linear trend line is defined by this equation: Y= a0 + b*X1 , in which we are assuming that

  • variable X is a timing factor (day, month, year etc..) and can be used to explain the fluctuation of the output Y;
  • a0 & b are the best estimators of the model and can be calculated using the ordinary least squares (OLS) method.

We define: x1=X1-Average[X1] and y=Y-Average[Y] then

  • b = Sum[x1*y]/Sum[x1*x1]
  • a0 = Average[Y] – b*Average[X1]

Similarly, a polynomial trend line can be defined by this equation: Y=a + b1*X1 + b2*X2, in which:

  • variable X1, X2 are timing factors (day, month, year etc..) and can be used to explain the fluctuation of the output Y;
  • X2 = X1 * X1
  • a, b1 & b2 are the best estimators of the model and can be calculated using the ordinary least squares (OLS) method.


We also define x2=X2-Average[X2] then

  • b1 = {Sum[x2*x2] * Sum[x1*y] – Sum[x1*x2] * Sum[x2*y]}/ {Sum[x1*x1] * Sum[x2*x2] – Sum[x1*x2] * Sum[x1*x2]}
  • b2 = {Sum[x1*x1] * Sum[x2*y] – Sum[x1*x2] * Sum[x1*y]}/ {Sum[x1*x1] * Sum[x2*x2] – Sum[x1*x2] * Sum[x1*x2]}
  • a = Average[Y] – b1*Average[X1] – b2*Average[X2]

Create a linear trend line in Webi 4.0

Step 1: Build a Webi report using eFashion Universe.

/wp-content/uploads/2014/02/step1_388545.png

Step 2: Create new variables for those in the warm-up reminders Section. Note that we don’t have to create a new variable for each of them.

Create X1 (assuming we are showing trend lines by month)

/wp-content/uploads/2014/02/step2_388546.png

Similarly, create x1y

=([X1]-(Average([X1]) In Block))*([Sales revenue]-(Average([Sales revenue]) In Block))

Create x1x1

=([X1]-(Average([X1]) In Block))*([X1]-(Average([X1]) In Block))

Create b

=(Sum([x1y]) In Block)/(Sum([x1x1]) In Block)

Create a0

=Average([Sales revenue]) In Block – [b]*(Average([X1]) In Block)

Create Linear Trend

=[a0]+[b]*[X1]

Step 3: Insert a webi chart with the linear trend line we have created:

Go to Report Element \ Chart \ Line

/wp-content/uploads/2014/02/step3_1_388565.png


Assign data to the new chart

Step3-2.png


Enjoy the result. The image below shows linear trend line and Sales revenue in DC only

Step3-3.png

Below is the Sales revenue Report for California

Step3-4.png


Create a polynomial trend line in Webi 4.0

Assuming we continue to use some of the work we have done in the Linear Trend Line section.


Step 4: Create additional variables for the polynomial trend line

Create X2

=[X1]*[X1]

Create x2x2

=([X2]-(Average([X2]) In Block))*([X2]-(Average([X2]) In Block))

Create x2y

=([X2]-(Average([X2]) In Block))*([Sales revenue]-(Average([Sales revenue]) In Block))

Create x1x2

=([X1]-(Average([X1]) In Block))*([X2]-(Average([X2]) In Block))

Create b1

=((Sum([x2x2]) In Block)*(Sum([x1y]) In Block)-(Sum([x1x2]) In Block)*(Sum([x2y]) In Block))/((Sum([x2x2]) In Block)*(Sum([x1x1]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1x2]) In Block))

Create b2

=((Sum([x1x1]) In Block)*(Sum([x2y]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1y]) In Block))/((Sum([x2x2]) In Block)*(Sum([x1x1]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1x2]) In Block))


Create a

=(Average([Sales revenue]) In Block)-[b1]*(Average([X1]) In Block)-[b2]*(Average([X2]) In Block)

Create Poly Trend

=[a]+[b1]*[X1]+[b2]*[X2]

Step 5: Add the polynomial trend line in the current chart

Right-click on the chart then choose Assign Data…

Step5.png

Click on the plus ➕ sign in the Value Axis 1 Section, then choose Poly Trend.

/wp-content/uploads/2014/02/step5_2_388573.png

Enjoy the result.

/wp-content/uploads/2014/02/step5_3_388574.png

If you have any questions, please leave a comment below and I will try to answer them as soon as I can.

Happy Valentine!

BONUS: R-squared calculations

As Josh Crawford‘s suggested, I have included here a bonus section for R-squared calculation. If you need to refresh your mind about what it is, here is the link Coefficient of determination – Wikipedia, the free encyclopedia

Create SStotal

=([Sales revenue]-(Average([Sales revenue]) In Block))*([Sales revenue]-(Average([Sales revenue]) In Block))

Create SSres.Linear

=([Linear Trend]-[Sales revenue])*([Linear Trend]-[Sales revenue])

Create SSres.Poly

=([Poly Trend]-[Sales revenue])*([Poly Trend]-[Sales revenue])

Create R-squared.Linear

=1-(Sum([SSres.Linear]) In Block)/(Sum([SStotal]) In Block)

Create R-squared.Poly

=1-(Sum([SSres.Poly]) In Block)/(Sum([SStotal]) In Block)

If you place R-squared.Linear and R-squared.Poly next to each other in the table, you will see the values as shown here

Untitled.jpg

Here is the chart with both Linear and Polynomial Trend Lines using Excel:

Untitled1.jpg

Thanks,

Huu Nguyen


Assigned tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Beautiful... works like a charm.  How about for calculating R-squared values? 😉

      Author's profile photo Former Member
      Former Member

      Thanks for your feedback. I will update this thread with R-squared calcultion soon.

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Great Document,

      Surely this is going to save a lot of time for our SDN colleagues who have similar requirement.

      Author's profile photo Former Member
      Former Member

      Thanks Prabhith! It would be nice if this becomes a new feature in BO.

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Former Member
      Former Member

      great work. thanks

      Author's profile photo Former Member
      Former Member

      Excellent work Huu!  Now if only there was an easy way to do this in Design Studio...

      Author's profile photo Former Member
      Former Member

      Not working for me. Please could you attach your webi report  to this article?

      [Edit]

      Wait! Now It works, however since I only have real data up to 2005, how do I make it generate the possible values for 2006, 2007. Would it be more like a forecasting? Is it posible to achieve that on Webintelligence?

      ScreenShot780.jpg

      Author's profile photo Former Member
      Former Member

      We have a requirement to chart forecasted trend line based on the Linear Trend line. 

      1) How to add n # of months to the report date range and

      2) Include in chart the projected value i.e. in this example that would be the forecasted sales revenue for say the next 3 months. 

      Has anyone been able to do this or can someone provide steps for doing this?

      Author's profile photo Former Member
      Former Member

      I was able to generate trending values for periods I didn't have values. What I did was that I included in my Excel sheet those periods. In Webi it looks like this:

      A.png

      Then I applied linear trend formula

      /wp-content/uploads/2015/04/b_692369.png

      Author's profile photo Former Member
      Former Member

      Thank you Erika!  Can you show me what your calculation for the forecast column looks like? And the calculation for the 1.97 as well?

      Author's profile photo Former Member
      Former Member

      Trend

      ScreenShot003.jpg

      where m (slope  1.97) is:

      =(Sum([g2]-[g1]))/(Sum([p2]-[p1]))

      x is:

      =[Period]


      g2, g1 are my measures

      g2 is:

      =([measure] Where ([Period]=[p2])) In Block

      g1 is

      =([measure] Where ([Period]=1)) In Break

      p2, p1 are my periods

      p2 is:

      =Max(  [Period] Where ( Not(IsNull([measure]))   )     ) In Block

      p1 is:

      =Min([Period]) In Break

      b is

      =[g1]-([m]*[p1])

      Author's profile photo Former Member
      Former Member

      How do you get the period to extend beyond line 29?  Mine ends with the last month I have data for.  So there are no rows for the forecast values

      Author's profile photo Former Member
      Former Member

      You should have additional rows for additional periods in your query. It doesn't matter if they don't have data. It doesn't matter if you add them with a view or a union.

      For example, in my test, I added manually those rows (in table or excel) for additional periods with empty measures, since those measures will be calculated later.

      ScreenShot456.jpg

      Author's profile photo Former Member
      Former Member

      Excellent post! Thank you for sharing.

      Author's profile photo Former Member
      Former Member

      Interpolation can also be used where there are null values in the measure.

      My method is somewhat different to yours Huu but works in a similar way.  But I use interpolation to resolve the issue with null value.

      That is a post in istself though! 🙂

      Author's profile photo Former Member
      Former Member

      Hi experts,

         I wanna draw a linear trend line in Webi , and i want the resule like excel(as attachment) .

         QQ截图20151202004211.jpg

      Author's profile photo Marc Coca
      Marc Coca

      Hi everyone!

      First of all, awesome tutorial! I’ve been able to build a lineal and a polynomial (2 degree).

      However, I’m stacked with building interpolation like Excel does (x steps ahead).

      Yes, I expand the date vector to get more dates than available with data.

      So, my question is: assuming I have a polynomial trend line, how can I interpolate it 14 days ahead (for example) without affecting the coefficients?

      Any ideas???

      Many thanks!