topic Excluding data in expression; Converting values in Script Editor in QlikView

Excluding data in expression; Converting values in Script Editor

Thu, 19 Aug 2010 16:24:05 GMT

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!

Excluding data in expression; Converting values in Script Editor

vgutkovsky — Thu, 19 Aug 2010 17:09:09 GMT

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,

Excluding data in expression; Converting values in Script Editor

Thu, 19 Aug 2010 18:18:01 GMT

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.

Excluding data in expression; Converting values in Script Editor

vgutkovsky — Thu, 19 Aug 2010 18:44:00 GMT

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

Regards,