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