A Control Chart in Business Objects

This week, I read about Control charts on the Business Objects forum. I had not heard of the notion before, but of course, it didn’t take long to find more about it. WIKIPEDIA is your friend in times like these.
As it turns out, it is a line chart, enriched with a series of constants being:

• The mean or average
• The mean + and – twice the stdDev()
• The mean + and – three times the stdDev()

All of which can be done very easily.
When writing these functions, there are only two things you need to add to your calculations : “in body” and “in report”. I’m going to do this in Rich Client, but this works just as fine in any other version of SAP BusinessObjects.
The function for the mean looks like this :
=average([Sales revenue] in ([Month]) in Report (a variable which I call AverageSalesRevenue)
the function for the Standard deviation looks like
=stdDevP(<Sales revenue> in ([Month])) in Report (a variable which I call deviationRevenue)
please note that I’m using the stdDevP function and not the stdDev function, but that’s because I have all data accounted for. In case you made a random selection, you will need to use stdDev instead.

Basically, all you need to do is create 4 variables using the before mentioned functions.
1) [AverageSalesRevenue]+ ([deviationRevenue]*3) –> [AveragePlus3Deviations]
2) [AverageSalesRevenue] + ([deviationRevenue]*2) –> [AveragePlus2Deviations]
3) [AverageSalesRevenue] - ([deviationRevenue]*2) –> [AverageMinus2Deviations]
4)[AverageSalesRevenue] - ([deviationRevenue]*3) –> [AverageMinus3Deviations]
In View structure, it’s easy to add them to the linechart like such :

The rest is easy, just drag the documentation cells in place with formula :
="UCL:"+[average plus 3 deviations]
="Center line:"+[Average]
="LCL:"+[Average minus 3 deviations]

There, hope you enjoyed that,

Peter De Rop

Colouring alternating lines in Business Objects DeskI

This functionality exists by default in WEBI, but DeskI simply doesn’t have it:

But, as you can see, you can have it, if you really want to. Here is how :
First, we need to create a variable called linenumber, with the function :
=LineNumber() –1
The –1 is because linenumber also counts the header and therefore, starts at 2.
The second variable I create is called Even and the function is :
=Even(<linenumber>)
The rest is easy, create an alerter on that Even variable :

Easy
Enjoy


Peter De Rop

A Mini Chart in Business Objects WEBI

This is a nifty little trick you can pull with Business Objects WEBI. Yes, only WEBI-users will be able to do this, the new functions were only added in the WEBI version of Business Objects (but that includes Rich Client of course).

This is the end-result. In the column to the right of your numbers, you get some sort of a chart that shows the proportional importance of a number (to get this to work with negative numbers, you’ll have to complicate things somewhat)
In Excel, the function is =rept(“|”, yournumber) – in Business Objects, that function is called : Fill()
In this case, I also divided my numbers by 10000 to keep the charts inside the column.
So: =fill(“|”;[Sales revenue]/10000).
Ah, I set the fontsize to 5, to make it even better.
Short and sweet, don’t you think
Enjoy,


Peter De Rop

Stopping gaps in Business Objects Charts

This is a classic.When you’ve been creating charts in Business Objects, you know about this one. Let’s start by looking at the data we would want to present in a chart:

As you notice, there are a few months missing. They’re simply not there because we didn’t sell that particular product in that particular month at all. Still, we wish to present the data in a line-chart and display a line across those missing months, without leaving a gap.

For you WEBI users, we have a solution.

The only solution is adding a second query to your report, where you have all 12 months.

In your query editor, click : Add Query and select only the month dimension.

Once you did that and you added the month to the chart (instead of the month from the first query)

your Chart will look like :

Not much of an improvement.

This is what it looks like in WEBI

and with the extra query added :

 

For now, in DeskI, this is how far you could get. The only Solution that remains for DeskI is to calculate the rest of the chart by hand (which is what i will show in a next blogpost. In WEBI however, we have a much better solution : the function “interpolation”.

=Interpolation([Sales revenue])

Obviously, the numbers we get this way are fictious. They just serve to close the gap on the chart. It’s clearly the purpose to use the previous display of the data, but we use these numbers for our chart – this results in :

And we have a much cleaner chart.

Hope you find this useful.

Oh, and Mind the Gap

Cheers,

Binabik

An Exponential Trendline in Business Objects

This is going to be the third type of Trendline and I just noticed, Excel has six of them. I’m not sure how many really are used, or if there is a specific need for any in particular. So, for now, I’m gonna keep this at three, unless there is a demand for the other three. (Polynomial, Power and Moving Average).

I am going to add those three eventually, just for fun, but I’m going to keep them for a cold winter evening. Or when the question arises.

So, here’s the Exponential Trendline.

The magic number for the Exponential trendline is e. I’ve been reading about the constant e on Wikipedia and it’s a great story. You should read it too. One of the things I noticed is the amount of history that lies at the base of these trendlines. Amazing.

First, the formula:

Where c and b are constants. Each time when they say that (and here is where I find that info) I’m a bit pissed off. Especially, because they’re not constants at all. They’re calculated values. All of them.

c, as usual, is the intercept point but this time, it uses the Natural Logarithm of Y and the value (non Logarithm) of X. In fact, the calculation uses the EXP function, which is the inverse of a Natural Logarithm.

So, if i = ln(x), then exp(i) = x.

b is the coëfficient which we calculated in both other trendlines before, but this time, it uses the ln of  y.

(ah, on a sidenote, I wrote 2011 in my report, but on the test-databases that come with Business Objects, the numbers are identical for 2003 or 2006 –amazing huh )

So, I take the ln on Sales Revenue and I call it LnY

The formulas are:

=ln(<Sales revenue> in body) in report (which is called LnY)

=Average(<LnY> in body) in Report (which I call AverageLnY)

Like in the linear trendline, we calculate X-AverageX where X is a Runningcount of the Month.

=((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month>

=(<LnY> - <AverageLnY>) In <Month> becomes the variable LnY-AverageLnY

and we multiply those two :

=<LnY-AverageLnY>*<X - average X>

After which we add them up. As before, we now have the top of our division:

=Sum((<LnY-AverageLnY> * <X - average X>) In  Body) In Report

For the bottom part of our division, we need the Square of X-AverageX added up.

=Power(<X - average X>, 2) In <Month>

Now we divide those numbers

There, that’s our b-variable which we can use to calculate our c variable.

=<LnY>-(<LnCoeff>*<xvalue>)

There we go, we have both variables to put into our formula.

e = 2.71828, I found that on Wikipedia.

Now, one more thing needs to be done, and it’s probably why this is called the exponential trendline, we need to take the EXP of our intercept point (our c-variable)

=Exp(<InterceptLn>) In Report

=<ExpIntercept>*(Power(2.71828 ,(<LnCoeff>*<xvalue>)))

this is the formulat shown at the top, turned Business Objects. We add that one and convert it to a table. That gives us the following chart:

And excel does this :

Again, a perfect match.

There. That gives you Linear, Logarithmic and Exponential trendlines to add to your charts.

Enjoy

Binabik

Logarithmic Trendline in Business Objects

Right, looks like I overestimated this one. After doing the previous one, Logarithmic just sounded a bit more scary. It really isn’t. In fact, it was the Microsoft site that got me a bit going. They mentioned something called “Linear Regression”. I looked up the math and it looked impressive. I had no idea, that was what I had already done in the previous article.

That has been my experience so far. A series of terms is unknown to me. This makes it fun to figure out how this stuff works because I really have to figure it out for myself.

Like the function in excel to calculate the Intercept point. On one site, I found this construction :

=index(linest(A1:A20;B1;B20);1)

Then, I found that the intercept function does exactly that. =intercept(A1:A20;B1:B20)

Ah well. it’s an amazing journey – and I’m enjoying every stop along the way.But without further ado, here’s the Logarithmic Trendline.

First off, the math is exactly the same as we had in the Linear trendline except that we don’t use x but ln(x). which is the natural logarithm of x.

for this, I use the function =Ln(runningcount(<Month>)) in <Month>

As with last time, I try to avoid using the month in a calculation, although it would work just the same.

Next up, we calculate the average of that column. In fact, that’s the only difference with the linear trendline.

=Average(LnX in Body) in Report is the function I use for that one.

Like with the linear trendline, we then need to make the difference between X and average X (this time with the logarithms of course) and Y- averageY.

=LnX – AverageLnX in Month

=Y – AverageY in Month

Next, we multiply those two with eachother and add them up.

=LnX-AverageLnX * Y-AverageY

=Sum(LnX-AverageLnX*Y-AverageY in body) in report

That last one is going to be the first part of our division.

Like last time, we now need to Square X-AverageX, but this time, it has to be the Ln version. So, the formula would be:

=sum(ln(x)-AverageLnX * ln(x)-AverageLnX  in body)in report

That’s going to be the second part of our division.

Next, we divide those two.

And we have our Direction Coëfficient.

Next up, we need the interception.

For this one, we need to subtract from AverageY

=<AverageY> – (<AverageLnX>*<LnCoëfficiënt>)

This formula, again, is the same as Linear trendline, but we use the one where we calculate the logarithm of X.

the next formula is : (Coefficient*LnX)+ InterceptLn

And then, we convert this baby to a chart.

And, it has the typical Logarithm curvature

Here is the version Excel comes up with – and again, they are identical.

There. A logarithmic trendline is perfectly possible in Business Objects. One less reason to convert your Business Objects Report into an Excel sheet.

Next up : Exponential Trendline

Have a nice day !!

Binabik

Trendlines in Business Objects

This is what we would want.A trendline in Business Objects.

 

It is not possible to let Business Objects insert a trendline in a chart automatically. It’s a feature that is simply missing. This is what I have been living with until now, it simply isn’t possible, end of story. And that makes me rebel. I want to make it possible.

Two problems : I’m not a real good mathematician. The math to achieve trendlines isn’t easy math either.

But, good news, I figured it out – and it works. Over the next few articles, I will hand you the math to add trendlines to your charts.

Our first type of trendline : A LINEAR TRENDLINE

The functions Excel uses to calculate that trendline looks like this :

and this

That’s the sort of math that makes my head spin – but after 5 minutes, I figured out how it works and then it’s easy. In case you don’t know, the x and y with a dash over their heads are averages.

The first function gets you the “direction-coëfficiënt”, the second gives you the interception point. Both are needed to calculate the trend.

Step1:

Calculate the average on both X and Y.

In this case, I used the formula : Average(<Sales revenue) in Report. If you don’t know what the “in Report” stands for, you should have a look at the articles about contexts. It’s not an easy subject.

With that, we have the average of Y and we also need the average of X. This might be a bit strange. Basically, you just need to find the middle of the X-axis of your chart.

So, we need to count how many items there are and find the middle of that. In fact, for our 12 months the middle is not 6 but 6,5 because it’s an even number of lines. So, what I end up doing is using a runningcount function :”=RunningCount(<Month)” Which I then take an average on.

the function at the end is : =Average((RunningCount(<Month>) )) In Report. which I define as variable <AverageX>

This means, that at this point, we have the two numbers with the dashes over their heads. Next, we need to subtract the X and Y values from it.

For X, this is : =((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month> which I define as a variable X – AverageX

Next, we need Y – AverageY

=(<Sales revenue> - <AverageY>) In <Month> which I define as Y – AverageY

Next, we need to multiply those : =<X - average X>*<Y - AverageY> and add them up.

This gives us the first part of our division, specified at the beginning of this document.

For our second part, we need to Square x-divisionX and add them up.

that’s :=Sum(Power(<X - average X> , 2) In  Body) In Report

Last but not least, we need to divide them to get the direction Coëfficient.

Now that we calculated b, we can proceed to calculate

This translates to :

=<AverageY> - (<AverageX>*<coefficient>)

and that’s our “Intercept” which amounts to the Intercept function in Excel.

Now we can calculate the values for our Trendline. The function in this case (for a linear Trendline is:

y = mx + b

m = direction coëfficient

b = intercept

the x-Value is our runningCount on the month (remember)

So, now we transform this to a chart :

I had a great time trying to figure this one out, so I hope it’s useful to anyone. Next up Logarithmic trendlines. It won’t be easy, I can promise you that

 

ah.. last but not least, this is what that trendline looks like in Excel

Isn’t that – entirely – identical Lovely.

Cheers

Binabik