Workdays between in Business Objects

Excel has a nice function : networkdays() -- it will show you the net amount of working days between two dates (excluding weekends).

Business Objects doesn't have this -- it only has "daysbetween()"

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5) + ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

This is the formula I found on
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/

I had worked out a procedure of my own, but this one is way shorter -- although not easier :D

let's break it down:

=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5)

First we get the number before the comma, when dividing the number of days between two dates by 7. That gives us the number of entire weeks in the period.

Next, that number is multiplied by 5 and we have the basic number of weeks.

But because we did a Truncate, we lost a few days.

And that's part of the formula I'm taking my hat off for.. wow..

=ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

One day I'll be able to write constructions like these :)

Peter

Verifying Bank account numbers in Business Objects

Today, I was asked to do a data-quality check on bank account numbers -- of course, since I live in Belgium, I will focus on the Belgian bank account numbers, but the system is pretty much the same elsewhere -- and I'll be focussing on the limitations of Business Objects and how to get around them.

The basic calculation function that is being used is Modulo 97.

We'll be considering the following fictious IBAN Bank account number:

BE74857221143209

(I used http://www.mobilefish.com/services/random_iban_generator/random_iban_generator.php to generate a random number)

The first part we need to do, is move the left 4 characters to the right.

[strInvertedBacc]=Substr("BE74857221143209";5;Length("BE74857221143209")-4) + Left("BE74857221143209";4)

Which gives us:

857221143209BE74

Next, we need to replace the letters with numbers.

A = 10
B = 11
C = 12
D = 13
E = 14
....
Z = 35

Replace(Replace([strInvertedBacc];"B";"11");"E";"14")

I know this is a shortcut -- but I'm being pragmatic here -- to take into account all possible countrycodes would make the code way too complex.

So now our number has become:

857221143209111474

Now we perform the function tonumber() on it and..

Here we have the first indication why I'm writing this article. Business Objects makes a mistake and the number displayed is :

857221143209111000

That would be bad.

The solution in Business Objects is to treat the number in two halves. Excel has an even lower treshhold for error on this kind of calculation. There you have to work in groups of 5.


Mod(ToNumber(Left("857221143209111474";9));97)

gives us our left numbers

[leftnumbers] = 36

We turn that number into a string and we stick the right 9 numbers to it

=formatnumber([leftnumbers];"0") + Right(857221143209111474;9)

[newnumber] = 36209111474

which we turn into a number
And we perform another Modulo 97 on that:

= mod(tonumber([newnumber]);97)

= 1

And if the result is 1 -- the number is correct.

The total formula is:

=Mod(ToNumber(FormatNumber(Mod(ToNumber(Left("857221143209111474";9));97);"0") + Right("857221143209111474";9));97)

That's how it's done.

Enjoy,

Peter