Excluding data in expression; Converting values in Script Editor

[#1]

QUESTION 1: I'm trying to create an expression for a chart that, but I'm having some difficulty with the syntax.

The operation I want to perform is simply the [sum x/sum y] BUT restricted only to rows where values of X and Y are > 0.

If I type this in an expression window, all items are colored as expected, indicating my syntax is correct:
Where (MinutesWorked)>0 and (ResolutionTime)>0

And this, alone, when entered into the expression window appears correct as well:
Sum (MinutesWorked)/Sum (ResolutionTime)

But how do I join them?

If I put this in an expression window, everything after the second 0 is black, indicating the syntax is wrong:
Where (MinutesWorked)>0 and (ResolutionTime)>0 (Sum (MinutesWorked)/Sum (ResolutionTime))

QUESTION 2: Making values in a column equivalent.

In my dataset, I found that a column for "Status" has two equivalent but differently spelled values--"On-Hold" and "On hold".

What would be the correct syntax in the Script editor for converting all values of "On hold" to "On-Hold" in the column "Status"?

Thanks so much for your input!

[vgutkovsky · Accepted solution]

Answer 1: sum(if(x>0,x))/sum(if(y>0,y)). You can't use "where" in a chart expression; that's only valid syntax for the script. Alternatively, you can also use set analysis: sum({<X={">0"}>} X)/sum({<Y={">0"}>} Y)

Answer 2: Well, I can tell you right off the bat that it's preferable to scrub the data before you load it into QlikView. But if you want to make the conversion within QlikView, then the syntax would be: if(Status='On hold','On-Hold',Status) as Status

Regards,

[#3]

Thanks so much for your help, Vlad! With your guidance, I was able to refine the chart expression even further:
sum(if(x>0 and y>0,x))/sum(if(y>0 and x>0,y))

That allows me to remove from the calculation any row that has a value for x or y <0.

[vgutkovsky]

No problem, glad I could help. Try the same thing with set analysis, it should be faster.

Regards,