Skip to Content

This is my first blog in SAP Community. I would like to share the method to create exponential trendlines in Web Intelligence like exponential trendlines in Excel.

Now, Web Intelligence (Webi V. 4.1 SP.6) have no function to create trendlines directly as Excel. But we can apply exist abilities of Webi to create. Let’s Fun.

My Requirement : I want to create this chart and trendlines using Webi like output chart below.
In Excel we can create trendline by (Right Click at bar chart > Add trendline… > In Trendline Option > Select Trend/Regression Type).

How I can do?


Figure 1

In this I will share the method to create only Exponential Trendlines.
Let’s Create Chart and Trendlines using Webi !!


STEP 1:
 Understand Exponential Trend Formula.

Figure 2

STEP 2: Prepare variables for create trendline according to Exponential Trend Formula.


Figure 3

Vaiable Formula
x = RunningCount([Month])  Where (IsNull([Sales ]) <>1)
Avg x = Average([x])  In Block
x-Avg x = ([x] – [Avg x])  Where (IsNull([Sales ]) <>1)
(x-Avg x)² = Power([x] – [Avg x]; 2)  Where (IsNull([Sales ]) <>1)
Ln y = Ln([Sales ])
Avg Ln y = Average([Ln y]) In Block
Ln y – Avg Ln y = ([Ln y] – [Avg Ln y]) Where (IsNull([Sales ]) <>1)
(Ln y – Avg Ln y) * (x-Avg x) = (([Ln y] – [Avg Ln y]) * ([x]-[Avg x])) Where (IsNull([Sales ]) <>1)
b = Sum(([Ln y] – [Avg Ln y]) * ([x]-[Avg x])) Where (IsNull([Sales ]) <>1) In Block /
Sum([(x-Avg x)^2]) In Block
c = Exp([Avg Ln y] – ([b]*[Avg x]))


STEP 3:
 Combine variable as “Exponential Trendline” or “y” in Exponential Trend Formula above

Figure 4

Vaiable Formula
Exponential Trendline =[c]*Exp([b]*[No. of Month])

STEP 4: Create graph and assign data to the graph.

1) Select bar chart, then assign data like figure 5 below and OK.


Figure 5

2) Adjust graph format and layout.

Right click at chart > Format Chart > Global > Measure Properties >Select Exponential Trendlines Region Type as Lines > OK
(or adjust other properties as you need)

Figure 6

Result

Sales Amount of XYZ Company

Figure 7

**Noted: Data to calculate Trend value must greater than 0.

If there are any mistake don’t hesitate to comment or any advice and suggestions will be greatly appreciated.

Thankyou very much.

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply