topic Re: Division zero constraint in QlikView

Division zero constraint

linoyel — Wed, 12 Feb 2014 10:08:08 GMT

Hi,

In chart dimension calculation, I have a division, but we know that it's impossible to divide into zero. Here's my calculation:

(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1

The result is percent.

If (Sum(COST)+ Sum(SALARY) = 0, then the result should be 100%.

How to write it? Pls help!

Re: Division zero constraint

luis_pimentel — Wed, 12 Feb 2014 10:11:53 GMT

Try something like:

IF ( (Sum(COST)+ Sum(SALARY) = 0, 1,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 )

Re: Division zero constraint

kiranmanoharrode — Wed, 12 Feb 2014 10:12:05 GMT

Hello Linoy,

Please post a sample(Scenario) application for better understanding

Regards,

Kiran

Re: Division zero constraint

Wed, 12 Feb 2014 10:13:29 GMT

HI linoy

try this expression:

if((sum(Cost)+sum(Salary))=0,1,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 )

Re: Division zero constraint

vikasmahajan — Wed, 12 Feb 2014 10:13:45 GMT

Expression is

IF( Sum(COST)+ Sum(SALARY) >0,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 ,0)

in Numbers select show in %.

Vikas

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 10:22:45 GMT

Well, I'm confused myself.. no, it's not supposed to be 100%, cause let's say if:

case 1:

I invested 100 and sold 120, so my ROI would be ((120/100)-1)*100 = 20%

case 2:

I invested 100 and sold 80, so my ROI would be ((80/100)-1)*100 = -20%

case 3:

WHAT HAPPENS IF I INVEST 0 and sell 100??

What's my ROI then??

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 10:25:46 GMT

The thing is that if Sum(COST)+ Sum(SALARY) = 0, then the result is not supposed to be 0 but quite an opposite!

This part (Sum(COST)+ Sum(SALARY)) is my investment. If it's 0, it means that all Sum(INCOME) is a benefit. How much it will be in percents though?..

Re: Division zero constraint

vikasmahajan — Wed, 12 Feb 2014 10:36:51 GMT

Request you to share sample application so that help you.

Vikas

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 10:45:43 GMT

case 1:

I invested 100 and sold 120, so my ROI would be ((120/100)-1)*100 = 20%

case 2:

I invested 100 and sold 80, so my ROI would be ((80/100)-1)*100 = -20%

case 3:

WHAT HAPPENS IF I INVEST 0 and sell 100??

What's my ROI then?? (logically)

Re: Division zero constraint

Wed, 12 Feb 2014 10:52:14 GMT

Logically, then ROI is infinity.

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 11:01:58 GMT

If there is no investment (Cost of investment = 0), how would I present ROI? It's actually an infinite number... It's impossible, right?

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 11:09:29 GMT

Exactly! But it's not good to show "-" in the report... It's just not right... because then I do SUM(ROI) and I get less then it's supposed to be. Guess I'll just ask business what they want me to do in this case.

Re: Division zero constraint

Wed, 12 Feb 2014 11:10:40 GMT

It all depends on what you mean by saying "impossible". It's impossible in real life, but can be in your data source by some kind of reason (error, rounding, etc). So YOU must decide, how to display the result of such occasion. A real big value, a text like "error!" or "infinity" or just to substitute a little fixed value to costs (like 0.000001) - every way is possible using if(...) solution posted above.

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 11:10:49 GMT

Thank you! That's the right answer to the original question

Though after posting it, I understood that it's supposed to be smth else then a 100% (basically, I'm thinking how to present infinity lol)

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 11:18:02 GMT

I agree. It's not a mistake, it happens in real life. I'll think further how to present it Thanks for thinking with me

Re: Division zero constraint

Wed, 12 Feb 2014 11:22:06 GMT

I don't know your task, but SUM(ROI) is (logically) quite a strange operaion... Only if costs (investments) are equal for all the operations. Imagine: 1) inv=1mil, sales=0, 2) inv=1, sales=2. SUM(ROI) = 0, but I would kill such investment manager 😉

Re: Division zero constraint

linoyel — Wed, 12 Feb 2014 11:27:16 GMT

The business wanted to see ROI, but I have data when on certain days investment is 0 but there are sales, so ROI is "-". Sum(ROI) doesn't really make sense, as well as ROI calculation by itself. I'm getting to a conclusion that ROI might not be a right value to present in my case... already told the business about it...