A Pareto Chart

While I was writing the article about “Control charts”, I found out about “The Seven Basic Tools of Quality”. It was the first time I learned about that – and I realised that at least 4 of those seven tools can be built in Business Objects DeskI and WebI.

Those four tools are :

• A Control chart (described in a previous article)
• A Pareto chart
• A Histogram
• A Scatter Diagram

In this particular article, I wish to show you a Pareto Chart.

Basically, it is a chart that shows a bar and a line, where the line illustrates the importance of each of the bars.

This is accomplished by sorting the values from high to low and presenting them on a bar-chart. And then, adding the RunningSum/Total on a linechart.

The above chart shows that 50% of our reservations are made in 3 months April, August and February.

I realise that this isn’t the typical sort of data on which you use this particular chart, but I do believe this can be a useful way of presenting pretty much any kind of number. Where you want to compare your number to a % in total.

The example used on WikiPedia is various sources of absenteism. In an other source, I found the possible deseases among cattle, in order to find the deseases you want to tackle first.

So, enough talk. Without any further ado: The Pareto Chart.

Step 1. List your data in a table and sort it Large to Small :

Step 2 :

Calculate the Runningsum and divide by the total:

=RunningSum([Future guests])/Sum([Future guests]) in Report * 100

I multiply times 100 to make it a bit cleaner on the z-axis of the chart.

As soon as you do this however, any sorting on the table is gone, so we’ll have to re-sort.

Step 3: Click View structure, sort the measure you want to have sorted:

Second problem, the scale is wrong. It should be from 0 to 100. On the properties tab, make sure you set it to be 0 to 100. Given the type of number we have here, it will never be anything else than 0 to 100.

 

And we have ourselves a PARETO-chart.

Peter De Rop

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