Skip to Content

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


To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Erika Atencio

    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

    (0) 
  2. Ms Shawn Romano

    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?

    (0) 
    1. Erika Atencio

      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

      (0) 
    1. Erika Atencio

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

      (0) 
        1. Erika Atencio

          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

          (0) 
  3. Nicholas Lea-Trengrouse

    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! 🙂

    (0) 

Leave a Reply