Need help in the expression calculation

[#1]

Hi All,

I am using the below code in the expression in one of my pivot table to calculate growth but whenever column(1) is 0 it doesn't work as it should be. I know this will not work whenever the column(1) is zero. So let me know what changes has to be done in the expression to make it work.

(column(2)-column(1))/column(1)

Thanks and Regards,

Rikab Kothari

[Anonymous]

You can use condition:
if(column(1)=0, <some fixed value, for example 0>, <your current expression>)

[#3]

if(column(1)=0,column(2),(column(2)-column(1))/column(1))

If column 1 is 0 then answer will be column 2 as you cant take nothing away then divide it by nothing.

[#4]

Thanks for all your help,

I want something like this.

if column(1) = 0 and column(2)> 0 then value should be '1'

if column(1) > 0 and column(2)= 0 then value should be '-1'

if column(1) = 0 and column(2)= 0 then value should be '0'

else

if(column(1)=0,column(2),(column(2)-column(1))/column(1))

[#5]

What you are describing is more like the need for a flag which you could create in script.

A B FLAG

1 0 -1

0 0 0

0 1 1

1 1 2

You could possibly use nested if statements but it would get a bit complicated and would be better in script.

[Anonymous]

There is contradiction i your requirements:
In the 1st and 3rd lines: If column1=0, the result should be 1 or 0 depending on column1
But the last line says that If column1=0, the result should be column2.
?

[#7]

Hi Micheal,

That may incorrect! I got little confused!

Here is what i finally want

column(1) column(1) Result

0 >0 '1'

>0 0 '-1'

>0 >0 (column(2)-column(1))/column(1) // I want to use this calculation for this case

0 0 '0'

All these I want to do it expression only. I don't want to do anything in the script as it will be very difficult to maintain in the script for long term.

[sunil2288]

HI Rikab,

There may be smthng wrong you are xpecting..

Just check it out again..

Regards

Sunil

[Anonymous]

Rikab, here is the expression, assuming your 2nd column(1) is a mistyping and it should be column(2):

if(column(1)=0,
 if(column(2)=0, 0, 1),
 if(column(2)=0, -1, (column(2)-column(1))/column(1)))