topic Show trend when second value is zero in QlikView

Show trend when second value is zero

Mon, 02 Sep 2013 07:34:23 GMT

Hello,

This is a newbish questions,

But if i wanna show a trend based on two values i divied them,

So eg i have:

sum(amount) / sum(amount2)

Lets say

Sum(amount) = 1 and sum(amount2) = 0

Then i want a trend which says its 100% , but as everybody knows, you cannot divied by zero.

What can i do?

kaushiknsolanki — Mon, 02 Sep 2013 07:37:43 GMT

Hi,

You can use the if statement to check whether the sum(Amount2) is 0 or not, if its zero then replace it by 1.

in that case 1/1 will be 100%

Regards,

Kaushik Solanki

Mon, 02 Sep 2013 07:40:01 GMT

I already tried that, but i cannot seem to get it to work then?

I tried

=if(Sum({<CW={1}>} TotalRows) / sum({<PW={1}>} TotalRows) = 0,1,Sum({<CW={1}>} TotalRows) / sum({<PW={1}>} TotalRows))

However i might did something wrong?

v_iyyappan — Mon, 02 Sep 2013 07:41:37 GMT

Use like this

=If(Sum(amount2) > 0, Sum(amount) / Sum(amount2), Sum(amount))

Regards,

kaushiknsolanki — Mon, 02 Sep 2013 07:42:30 GMT

Hi,

its wrong cause

Sum({<CW={1}>} TotalRows) / sum({<PW={1}>} TotalRows) will never be zero when its 1/0.

so your expression should be

Sum({<CW={1}>} TotalRows) / if(sum({<PW={1}>} TotalRows)=0,1,sum({<PW={1}>} TotalRows))

Regards,

Kaushik Solanki

Mon, 02 Sep 2013 07:46:22 GMT

Ahhhh! Ofc!

Stupid me! Thanks alot!