The Tip Jar

Today, my wife asked me, how my Tip Jar was coming along. (The button you might have seen on the right)

I answered her truthfully:

I love you very much dear.

She said: So, how many people visit your blog these days?

I answered her truthfully:
About 2000 a month.

Wow, she replied, then that tip-jar idea I gave you must have really payed off right?

I answered truthfuly:
Not a penny dear.

Right, she replied, I'll cancel our holiday on the Bahama's then.

I agreed.

Can I write another article? I asked with a small voice.

Sure, she replied, are you going to leave the button on?

You never know, I responded. One day, a tip on this blog will be so helpful, a visitor will make a generous donation and we can have that holiday on the Bahama's.

I suppose one can dream, she replied-- a bit miffed.

I could bundle the blog and turn it into a book -- I suggested hopefully.

How are you going to turn the stuff you write about into an intriguing murder mystery that everyone wants to read? She asked jabbingly.

Hm.. I could change the name of my blog to "The Davinci code" I replied.

It's a start. She said, as she left the room.


Cheers folks,

Peter

Compound Annual Growth rate in Business Objects

To calculate the Compound Annual Growth rate, Excel has a function called XIRR. BO doesn't have that, but that's easy to remedy. All you need to do, is work out the following formula:

So, now we need a bunch of numbers and see where we get:

Right -- this has everything to do with investments, something I know nothing about, but here's some numbers to get us started :

On Jan 1 2010, we invest 10000€
On Jan 1 2011, our investment has grown to 13000€
On Jan 1 2012, our investment has grown to 15000€
and
finally, On Jan 1 2013, our investment has now a value of 19500€

The table looks like this in BO:

First, we'll divide our ending value by our beginning value -- interesting challenge.

Our last date would be : =Max([Date]) In Report = 1/1/2013

Our first date would be : =Min([Date]) In Report = 1/1/2010

So, our formula for that division becomes :

=[Value] where ([Date]=Max([Date]) In Report) / [Value] where ([Date]=Min([Date]) In Report)

I'll call this one basefigure

Now, we need to find out how many years it took:

The formula for that one is rather easy :

=Year(Max([Date]) In Report) - Year(Min([Date]) In Report)

And we divide 1 by that figure:

=1/(Year(Max([Date]) In Report) - Year(Min([Date]) In Report))

this one, I'll call exponent

There. Now, the next step would be to raise our first figure to the power of that second figure and subtract 1.

=Power([Basefigure];[Exponent])-1

Our Compound Annual Growth rate would be : 0.25 -- I guess those figures occurred in a parallel universe :)

Cool

Cheers folks

Peter

A running balance in Business Objects

I think it's quite clear by now, that it is my objective, never ever to need Excel -- well, almost. The charts in Excel do exceed BO's, but math -- naaaah.

Our next venture, is calculating a running balance.

In a running balance, we have a series of deposits and a series of withdrawals and we want to
keep track of the total of our balance at all times.

Here's how:

First, we need two columns. One has our deposits, the other has the withdrawals.

Now, we want to show a third column, with our balance -- on 1/1/2013, this should be : 180, on 2/1/2013, this should be -620 etc.

This is laughably easy really -- since BusinessObjects has a function Excel doesn't have: RunningSum

=runningsum([Deposit]) - runningsum([Withdrawal])

The result is :

In Excel this would be something like :

A	B	C
Deposits	Withdrawals	Balance
$1,000	$625	=SUM(A2,-B2)
$1000	740	=SUM(C2,A3,-B3)

Looks like our balance is in the red -- but with the economic times, that seems right :)

Cheers folks

Peter

Trimmed mean in Business Objects

This is a challenging one.

This is what the formula looks like:

But all this means is:

You pick a value k. And the % k number of values, is dropped while calculating the mean.

So, if k = 10% and you have 100 values, you'll be dropping the bottom 10 and the top 10 values and calculate the mean on the remaining 80.

I think it will be useful, to use ranking to calculate these.

First, lets calculate, from our list of products, with k = 30%, how many values we would need to drop.

We have 12 values. 30% of 12 = 4, so we'll be dropping the top 4 and the bottom 4 values.

Let's calculate this in Business Objects.

V_k = 0.3

=round(count([Lines]) * V_k,0)

which I will name V_ProductsToKeep

We need an integer of course, so round it is.

So, now we need to find out, what the 4th highest value is and filter everything out that is higher or equal. And we need to find out what the 4th lowest value is and filter everything out that is lower or equal.

=NoFilter(Rank([Sold at (unit price)];Top))

This gives us the Top  -- returning a value -- if V_ProductsToKeep is higher or equal than that value, we filter the value out.

Same with the lowest:

=NoFilter(Rank([Sold at (unit price)];Bottom))

This gives us the Bottom -- returning a value -- if V_ProductsToKeep is lower or equal, we filter that value out.

=If([V_Bottom] <= [V_ProductsToKeep]) Then (0) ElseIf ([V_Top]<=[V_ProductsToKeep]) Then 0 Else 1

Nice, now all we need is a formula, that determines what the sum is of [Sold at (unit price] where V_Evaluate = 1

=Sum([Sold at (unit price)])Where([V_Evaluate]=1) / ([V_NProducts] - ([V_ProductsToKeep]*2))

There. Not easy at all, but it works.

Our Trimmed mean with k = 33% is 166.6 -- higher than our regular mean, because we dropped the bottom and top 33% of the values.

If we change k to 0.1, the trimmed mean is:165.8

Good fun -- not that easy to do. I think that's going to be it for averages for now :)

Quadratic average in Business Objects

Next up: the Quadratic mean.

As usual, first the function:

This is quite a different animal. The application of it isn't really sales, on wikipedia it even says that this applies to electricity, but I don't have those kind of figures -- it'll work just as good with my numbers. It no longer takes into account the number of items sold, so our starting table is going to look a bit different:

From the looks of it, we first need a count of the number of entries and divide 1 by that number. In our case, this gives us the following formula:

=1/count([Lines])

In our case, this is 1/12 or : 0.08.

Next, we have to take the second power of each unit price.. easy peasy.

=power([Sold at (unit price)];2) 

And next, we need to add those up:

=Sum(Power([Sold at (unit price)];2))

Next, we have to multiply those and take the square-root.

=sqrt(Sum(Power([Sold at (unit price)];2))*(1/Count([Lines])))

The Quadratic mean of the unit price is 167 -- so far, that's the highest mean we got.

More averages coming up.

Peter

Weighted Harmonic Average in Business Objects

So, here's the second part of the Averages series. And this one might prove to be just a bit more challenging.

This time, we're going to calculate the Weighted Harmonic Average.

The formula I found on Wikipedia looks like this:

The table we're starting with, again, looks like this:

Our Average is 165.8, the median is 165.05 and the Weighted average is 163. Lets see what the Harmonic Weighted average gives us.

Lets add up our Unit Prices.

=sum([Quantity sold])

Now, we divide each quantity sold by the unit price and we add them up. Sounds weird to me, but, why not.

(Sum([Quantity sold]/[Sold at (unit price)])

I think I'm starting to like this particular average :)

Last step, we divide.

=Sum([Quantity sold]) / (Sum([Quantity sold]/[Sold at (unit price)]))

Tada, our Weighted Harmonic Average : 161.52

More averages to come :)

Peter

Calculating averages in Business Objects

Time for some averages.

Obviously, I'm not going to deal with the average function, that seems straightforward enough. This article and following articles will be about some other averages:


Weighted mean, Weighted harmonic mean, Quadratic mean, Winsor mean, Truncated mean, Mean of Angles, and so on and so forth. I didn't know there were that many means, so it's gonna be fun figuring some of them out. As I already pointed out in previous articles, I'm not a mathematician, so I'm learning this stuff as I write it.

Let's start with the Weighted mean.

The formula on Wikipedia looks like this:

Fortuntately, I had the chance of learning to read these syntaxes when I wrote the articles on trendlines. So, it's going to be just a little bit easier.

Basically, it's just going to be adding up a bunch of multiplications and dividing it again. Lets get a bunch of numbers:

Our starting table is this one -- as usual, I'm taking eFashion to start from:

We're interested in the weighted average of the Unit Price. The average is 165.80 and the median would be:165.05.

First, we multiply Quantity sold and unit price and we add them up.

In a column to the right, I'm adding =[Quantity sold]*[Sold at (unit price)]

Nice. Next, we divide that big number by the total of the quantities:

=sum([Quantity sold]*[Sold at (unit price)])/sum([Quantity sold])

This results in:

Our weighted mean is: 163

More means coming up.

Peter

ForceMerge

Quite a useful function I should say. I think it just might be the solution to much of the Multivalue errors I've been having in my job as a Reporting Officer.

Here's how ForceMerge works :

First, you make two queries. One contains the dimension you don't have in your second universe and a common field,the other one contains that common field and some calculated data.

The left table contains states and cities, the right one contains cities and Sales figures. What we would really want, is State and Sales figures.

So, we move the sales figures in with the left table ( first we merge the city object of course).

There.. looking great. Just, we don't want the city. we want the state. But when we remove it:

We get ourselves a cartesian product. The solution to this issue is: Forcemerge().

When we change the formula for the Sales figures to : =ForceMerge([Sales Revenue]), the following table emerges:

Ain't that a beauty :)

That's all folks.

Enjoy,

Peter

business objects operator "????"

I noticed that someone searched for these terms.

This is probably going to be short and sweet:

business objects operator "????"


Whenever you find ???? inside a variable, it simply means that the object that was used 

to create this particular variable, is no longer in the query.

Let's say that you create a variable Year. And you use the following formula:

=year([Sales date])

and then, remove the object [Sales date] from your query.

The object Year will then contain =year(????)

Not good :)

Cheers