The Second best

Again, on the BOB forum, I stumbled into this question.

This person wanted to show the second highest value in a list. The following procedure will allow you to pick which one you want to show.

The formula I will be using here is Rank

This is my starting table. I want to show only the second best of each year.
I create a variable, using the rank function

=Rank([Sales revenue])

This one won't cut it, because, when you filter on it, it will actually show you Q2 2004. Because the filter will actually operate in the context of the report.

The correct function is therefore :

=Rank([Sales revenue];Top;([Year]))

This will show the rank of each quarter, for each year. Next, we need to set a filter on that :

 

There, only the second best shows. Yes, the rank-value now shows 1 everywhere, since the item that still shows is now the only one to still be available.

To solve that one : 

=NoFilter(Rank([Sales revenue];Top;([Year])))

Now, it shows the number 2.

Enjoy,

Peter

The Awesome TimeDim function

Sometimes, you think you know BusinessObjects, and then, you find a function you wonder.. what on earth is that.

One of those is : TimeDim

It's awesome.

In short : it completes a list of dates.

So, if you have data for 2012/01/01, 2012/01/05, 2012/01/06 -- it completes the list by adding 2012/01/02, 2012/01/03 and 2012/01/04

This is a VERY useful function indeed.

In the screenshot above, the left table does not have timedim on it.. the right one, the function is : =timedim([Invoice Date])

Oh.. by the way.. the help on this function DOESN'T WORK. So, you have to figure it out for yourself :)

Cool huh.

And it gets better.

 

The left table in the above picture, is what our database returns. But I want to show the table to the right.

Again, The TimeDim function can help.

In the above tables, I used the following functions :

=Year([Invoice Date]) 

="Q"+Quarter([Invoice Date]) 

In the right table, I used :

=Year(TimeDim([Invoice Date];YearPeriod))  

and

="Q"+Quarter(TimeDim([Invoice Date];QuarterPeriod))

 As it turns out, this particular function was added in Service Pack 4 of Business Objects XI R3

Cheers,

Peter

My undo button does not work

This is a nice bit of frustration you might run into in BusinessObjects XI R3 WEBI.

Here's the situation : You have a report -- any report. When you move a table, delete a column, delete a cel, the undo button does not light up.

It's not a bug really, it's normal behaviour.

If you accidentally click the Show/Hide Filter toolbar button (to the left of the Drill-button) -- for some reason, the undo-function is de-activated.

So, if you run into this issue -- just de-activate that filterpane button.

Weird

Peter

Only showing lines with one or more empties

I noticed this question on the BOB forum -- and I thought I'd have a stab at it. Here goes:

Consider the following table :

 

Some months have data for all years, some only have data for one or two of the years. The person on the BOB forum asked, if it's possible to only show those where one or more of the years does not have data for one of the months.

Our first step is, we need to determine, just how many years there are.

=Count([Year] ) in Report - we call this calculation TotalNYears

Next, we need to determine, the number of years present for each of the months.

=Count([Year] )in ([Month]) - we call this one NYears

Next, we need to subtract those two.

=[TotalNYears] - [NYears] and we call that one : DifferenceNyears

and last but not least,

we filter that table for [DifferenceNYears]>0

 

Nice, isn't it ;) If you set the filter to : differenceNYears = 0 then, you only have those where each year has data. In fact, I remember writing a blogpost a few years back where I showed that last thing, and how to do it at the query level. It involved combining multiple queries.. this method is MUCH faster.

Peter

A Cumulative Index versus a Target

Let's assume, you need to attain a certain target each month. Yeah, I know, sounds farfetched - um - not.

At the end of the year, you need to attain the cumulated total of the monthly objectives.

So, every month, you would like to know, how well you did, so far, compared to the objectives of every month so far, and the total objective for the entire year.

Sounds like a mouthful -- believe me, it is. But it's fun -- you'll see.

The function we'll be using is : RunningSum()

Here's the table I'm starting with :

As you can see, it has numbers for each year and a target line. Now, we'll be adding a line underneath our Target line, that calculates the variance between 2012 and 2011.

The formula to do this is :

=(sum([Sales revenue]) where([Year] = "2012") /sum([Sales revenue]) where([Year] = "2011"))*100
(you could leave the *100 away and format the number as a percentage.. works just as fine)

 

There, that part is done. Now, we calculate the variance compared to the Target. That shouldn't be too difficult, since the formula is basically the same as the previous operation :

 =(sum([Sales revenue]) where([Year] = "2012") /[Target 2012])*100

 

Perfect.Next, the cumulative Index compared to the target.

The formula is almost the same :

 =(Runningsum([Sales revenue]where([Year] = "2012") ) /Runningsum([Target 2012]))*100

the MAJOR difference, is that you need to put the Where inside the brackets of the runningsum.

 

And, we have our cumulative total. Looking good. Last but not least, we could leave that first cel of our Cumulative variance empty, since it's the same as the line above.. so we elaborate our formula a little

=If ([Month]>1) Then ((CumulatieveSom([Sales revenue] Where([Year] = "2012")) / RunningSum([Target 2012])) * 100)

Lovely isn't it :)

Enjoy !

Peter

Grouping data

Time for a quite basic use of functions and formulas. This is stuff that used to be very automated in Desktop Intelligence -- with WebI, that has changed.

The way in BusinessObjects to group data, is the if function. Yes, I said it right, it's a function.

Lets say we wanted to group the following data :

ProductCode   Items sold
ACA               100
BAC               150
DTA               120
XCA               130
RRA               170
UUA              135

The first three are product group "Alpha", the last three are the product group "Beta". The number in the last column is, say, the number of items sold.

The syntax would therefore be :

=if([ProductCode] inlist("ACA";"BAC";"DTA")) then("Alpha") else("Beta")

The syntax somewhat changes, if you want it to be three groups.

=if([ProductCode] inlist("ACA";"BAC";"DTA")) then("Alpha") elseif([ProductCode] inlist("ACA";"BAC";"DTA")) then("Beta") else("Gamma")

As for you Dutch speaking people -- be warned, the translation of if then else in dutch is incorrect.

=Als([ProductCode] inlijst("ACA";"BAC";"DTA") then("Alpha") else("Beta")

they forgot to translate the then and else in dutch.

Now, lets consider the And operator :


ProductCode  Code    Items sold
ACA                  A          100
BAC                  A          150
DTA                  A           120
XCA                  A          130
ACA                  B           170
UUA                  A           135

If we performed the previous code, we would get some incorrect data.. so :

=If([ProductCode] InList("ACA";"BAC";"DTA") And [Code]="A") Then("Alpha") Else("Beta")

Enjoy

Peter