topic Need help in the expression calculation in QlikView

Need help in the expression calculation

Fri, 17 Sep 2010 13:54:14 GMT

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

Need help in the expression calculation

Anonymous — Fri, 17 Sep 2010 13:58:16 GMT

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

Need help in the expression calculation

Fri, 17 Sep 2010 14:51:36 GMT

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.

Need help in the expression calculation

Fri, 17 Sep 2010 15:13:05 GMT

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))

Need help in the expression calculation

Fri, 17 Sep 2010 15:25:13 GMT

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.

Need help in the expression calculation

Anonymous — Fri, 17 Sep 2010 17:59:26 GMT

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.
?

Need help in the expression calculation

Sat, 18 Sep 2010 07:47:22 GMT

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.

Need help in the expression calculation

sunil2288 — Sat, 18 Sep 2010 09:56:54 GMT

HI Rikab,

There may be smthng wrong you are xpecting..

Just check it out again..

Regards

Sunil

Need help in the expression calculation

Anonymous — Sat, 18 Sep 2010 17:01:29 GMT

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)))