Add a condition to a chart expression

[#1]

I'm evaluating Qlikview and hit a snag.

I have a table named cases. There are columns named 'casenumber', 'owner', and 'age'. Age is a number. Owner is a string.

I want to show the percentage of cases where Age < 7, per Owner. I thought I could accomplish this with a bar chart with a dimension (x-axis) of Owner, and an expression(y-axis) similar to this:

((numeric_count(casenumber) where Age < 7) / (numeric_count(casenumber) )) * 100

I know that syntax is bogus but that's the idea of what I am trying to do. Is putting this logic in the chart expression the right thing to do?

Thanks in advance for any guidance.

[johnw · Accepted solution]

Yes, I would probably put the logic in the chart expression, and I think your only problem is getting the right syntax. There are a couple major approaches. First is to just use an IF statement.

count(if(Age<7,casenumber)) / count(casenumber)

You don't need to multiply by 100 because you can display the result as a percent by going to the number tab in the chart properties and telling it to display as percent. So I never multiply by 100 for my percentages.

The second major approach is to use set analysis, assuming you have version 8.5 or later. Set analysis is typically faster, but the syntax can get complicated. It's not too bad in this case, though.

count({<Age={"<7"}>} casenumber) / count(casenumber)

Also note that in some data models, the case number may be repeated, and you may need to use the "distinct" keyword to get the right count:

count(distinct if(Age<7,casenumber)) / count(distinct casenumber)
count({<Age={"<7"}>} distinct casenumber) / count(distinct casenumber)

[#3]

Thanks John - That worked for me. Without having background with the tool wasn't sure whether I was barking up the correct tree.