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
We define: x1=X1-Average[X1] and y=Y-Average[Y] then
Similarly, a polynomial trend line can be defined by this equation: Y=a + b1*X1 + b2*X2, in which:
We also define x2=X2-Average[X2] then
Create a linear trend line in Webi 4.0
Step 1: Build a Webi report using eFashion Universe.
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)
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
Assign data to the new chart
Enjoy the result. The image below shows linear trend line and Sales revenue in DC only
Below is the Sales revenue Report for California
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…
Click on the plus ➕ sign in the Value Axis 1 Section, then choose Poly Trend.
Enjoy the result.
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
Here is the chart with both Linear and Polynomial Trend Lines using Excel:
Thanks,
Huu Nguyen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |