BusinessObjects Board

if function when value is blank

building reports webintelligence legacy
#1

Hello,

I’m new here and I tried searching, but I can’t make my function work.

I want to compare this year sales (by product) with last year’s and I use =[Sales2015]/[Sales2014]-1. Then I format as percentage.
Problem is that if [Sales2014] is blank or [Sales2015] is “0” I get result “-100%”, wich is mathematically understandable, but for such results I want to see blank cell.

I thought of using If funcion: If [Sales2015] or [Sales2014] value is blank (or 0) then result blank cell, else count growth ([Sales2015]/[Sales2014]-1)
But how to describe “is blank”? ="" ? And how to put “is blank” or “0” in the same function?

I tried, but not working:
=If [Sales2015] Or [Sales2014] = “” Or =0 Then “” Else [Sales2015]/[Sales2014]-1

Evaldas_LT (BOB member since 2015-08-28)

#2

Welcome to B:bob:B!

Use the IsNull()WebI function. Something like this:

=If Not( IsNull([Sales2015]) Or IsNull([Sales2014]) Or ([Sales2014] = 0) ) Then [Sales2015]/[Sales2014]-1

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Thank you for your quick answer.
Of course I don’t know exact syntax of BOE BI (especially spacing) yet :), but I used your syntax and I got error:
Missing operator or closing parenthesis in ‘Not’ at position 140. (IES 10067)

Note: name “Sales2014/2015” was example, in my report it has longer name.

Position 140 means after 140th character?
that means smth wrong here:
=If Not( IsNull([Sales2015]) Or IsNull([Sales2014]) Or ([Sales2014] = 0) ) [HERE] Then [Sales2015]/[Sales2014]-1

Evaldas_LT (BOB member since 2015-08-28)

#4

Hi,

Yes, that’s correct.

Can you please post your exact formula (using the real object names) that you tried and it did not work?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#5

Of course:

=If Not( IsNull([Policy GWP amount (group EUR)]) Or IsNull([Policy GWP amount (group EUR) YA] Or ([Policy GWP amount (group EUR) YA] = 0) ) Then [Policy GWP amount (group EUR)]/[Policy GWP amount (group EUR) YA]-1

“Policy GWP amount (group EUR)” is “Sales2015”
“Policy GWP amount (group EUR) YA” is “Sales2014”

Evaldas_LT (BOB member since 2015-08-28)

#6

Hi,

There was missing one right bracket in your syntax. Try this one if it works:

=If Not( IsNull([Policy GWP amount (group EUR)]) Or IsNull([Policy GWP amount (group EUR) YA]) Or ([Policy GWP amount (group EUR) YA] = 0) ) Then [Policy GWP amount (group EUR)]/[Policy GWP amount (group EUR) YA]-1

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#7

EXCELLENT! That worked! Thank you so much! :+1:
P.S. I’ll use this as an example when creating other formulas.

Evaldas_LT (BOB member since 2015-08-28)