BusinessObjects Board

Section calculating

building reports desktopintelligence
#1

Hi.

I use BO 6.5

I have a report looking like this:

–section-----------------------------------
+++++++A B
product a 1 3
product b -5 7
–section-----------------------------------
+++++++A B
product a 2 -4
product b -4 2
–Total---------------------------------------
A B
-2 6

The numbers in the section is a result of severel numbers beeing calculated.

If we look at product a. The sum of A and B is 4 in the first section and -2 in the second. I want to sum pr product the totals that are positive (greater then 0). In this case the result would be 4.

I have a varaible that sums A and B column called
So I have tried "= where ( > 0) which gives syntax error.

Skywise :norway: (BOB member since 2007-04-26)

#2

Try this: sum(if > 0 then else 0)

Gillian :uk: (BOB member since 2005-08-18)

#3

That gave me the total sum (including the negatives) I find that strange

Skywise :norway: (BOB member since 2007-04-26)

#4

Hi,
Check https://bobj-board.org/t/15226/18.
You can’t use the “>” operator on WHERE clause.
On link above there’s a suggestion to resolve the problem.

joaofpo :es: (BOB member since 2007-03-29)

#5

So, it resolved or not?
Give us a feedback.
:slight_smile:
Bye.

joaofpo :es: (BOB member since 2007-03-29)

#6

It worked yes.

Thank you. :slight_smile:

Skywise :norway: (BOB member since 2007-04-26)

#7

Except I found one issue…

When creating the var like this:= ( > 0) as Dimension

it worked only on some of the rows. When changing it to meassure it returnet 1 on all the positive

The object again is sum of 2 other objects and . And it seems like when is negativ it always returns 0. Even if the sum is positive.¨

is this: =(+)

Skywise :norway: (BOB member since 2007-04-26)

#8

Look:

Create a variable called FlagGreatherThanZero as dimension with this syntax:

(<A>+<B>) > 0

This variable will be 1 when the sum of + be positive and 0 when negative

Then create another variable, TotalSum, as measure:

=(<A>+<B>) Where (<FlagGreatherThanZero>=1)

So TotalSum only will show a vallue when + be positive.

Hope it works.

joaofpo :es: (BOB member since 2007-03-29)
#9

Yes, i have done that.
But in some occations it gives 0 when it should be 1

Ex.

A B Var Flag
2 -3 -1 0
-3 6 3 0 <- Should be 1 here
3 2 5 1

var = a + b

Skywise :norway: (BOB member since 2007-04-26)

#10

Also, when I copy the formula from the variable that gives zero where it should be 1. Then it works fine…

Skywise :norway: (BOB member since 2007-04-26)

#11

Well… I really don’t no. Is there any filter working on the table? It could explain. I re-created the report here and it works fine.

joaofpo :es: (BOB member since 2007-03-29)

#12

i did to. Even created a new univers. Very strange. The logic should be pretty simple.

I will see if I can find a work around.

thank you for helping.

Skywise :norway: (BOB member since 2007-04-26)