OK, I have a query that pulls in a field (pay period) which is alphanumeric. In my report, I need to divide by a substring of this field. So, what I need to do is take a substring of pay period, and then perform a ToNumber function on it so that I can use it in a calculation.

To do this, I create a variable that substrings it, then create a second one that changes that new variable to a number. I then try to use that variable in a calculation and I receive the #COMPUTATION error. I’ve used similar logic in other reports and it’s worked fine. Any ideas on what I may be doing wrong? Thank you in advance, this is KILLING me!

ryanferrario (BOB member since 2004-02-27)

What the formula causing a computation error? Which variables are displayed in the same block as the “problem” formula ?

Andreas (BOB member since 2002-06-20)

I have figured it out. Thank you to all who took time to respond (Andreas) or were thinking about it.

ryanferrario (BOB member since 2004-02-27)

Well, care to share what you did to solve your problem, please?

Andreas (BOB member since 2002-06-20)

Hi Andreas,

=Sum() Where (=2004) it’s working fine when i take user responce insted of 2004 it’s giving syntax error
=Sum() Where (=ToNumber(SubStr(UserResponse (“Query 1” , “Enter Year & month YYYY/MM”) ,1 ,4))) --this is throwing an error

Matt

matt_man2004 (BOB member since 2004-06-12)

Take a look at our FAQ: Reporter and you’ll find this entry.

I’m surprised that the “>=” even works – perhaps you’re on a newer version? Traditionally, the SUM (WHERE…) required that the operator needed to be an equal sign.

And, the operand to the right required a constant or a variable – no functions. So try creating a new variable called whatever you want, I"ll pick “X”:

X =ToNumber(SubStr(UserResponse (“Query 1” , “Enter Year & month YYYY/MM”) ,1 ,4))

and then change your SUM() Where (= X)

Anita Craig (BOB member since 2002-06-17)

Hi Anita, if you see there’s no “>=”, it’s <J Fiscal Year Id>=2004

Mario.

mhma1979 (BOB member since 2005-01-24)

Whew! Just my blurred vision kicking in!

Anita Craig (BOB member since 2002-06-17)

Hi Anitha,
I created exactly what u said here but it still giving syntax error

X =ToNumber(SubStr(UserResponse (“Query 1” , “Enter Year & month YYYY/MM”) ,1 ,4)) from this variable i am getting 2004,if i hardcode in place of X it’s working fine but if i use variable insted of X giving error.

=SUM(MTD Shipped Cases) Where (J Fiscal Year Id= X)

Thanks,
Matt

matt_man2004 (BOB member since 2004-06-12)

Hi matt!

is number or string?

Mario.

mhma1979 (BOB member since 2005-01-24)

Hi Mario,
data type is Number
Thanks,
Matt

matt_man2004 (BOB member since 2004-06-12)

Why don’t you create a formula like this:

If J Fiscal Year Id= X Then MTD Shipped Cases

And then sum that new variable in your column… did it work?

mhma1979 (BOB member since 2005-01-24)

Thanks Mario.
Actually this is a cross tab report if i use =If J Fiscal Year Id=X Then MTD Net Dollars giving me #computation error
if i included J Fiscal Year Id in cross tab then it’s giving multival error

Thanks,
Matt

matt_man2004 (BOB member since 2004-06-12)

Sorry, but I don’t get if that helped you or not :?

mhma1979 (BOB member since 2005-01-24)

Thanks
Matt

matt_man2004 (BOB member since 2004-06-12)

have you tried:

=If J Fiscal Year Id= X Then MTD Shipped Cases Else 0

and then sum this new variable?

mhma1979 (BOB member since 2005-01-24)

Sorry, my error – since “X” is a variable, you have to enclose it in the same angled-brackets as other variables:

=SUM() Where ( = )

Anita Craig (BOB member since 2002-06-17)