woensdag, november 30, 2011

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

Geen opmerkingen: