Displaying Expression in Chart Using Another Expression As Dimension

[#1]

Hi,

I am using QlikView 9 SR6 and I was wondering if there was a better way to solve this issue. I have a group of people that have a score related to them. What I would like to display is a bar chart showing the number of people with a score <= 10%, <= 20%, <= 30%, ... <= 90%, <= 100%. Not all scores are represented in the data set and no scores will ever be higher than 100%.

Right now I'm using these formulas as my calculated dimension and expression, respectively:

Calculated Dimension = Class((Numerator/Denominator) * 100, 10)
Expression = Sum(#PERSON_COUNTER * %HAS_RESULTS) - Sum(#PERSON_COUNTER * %EXCLUDED)

This works, but it has two "cosmetic" issues that I'd like to remedy:

1. The dimension labels are ugly. They show as 70 <= x < 80, 80 <= x < 90, 90 <= x < 100, 100 <= x < 110. I know I can replace the 'x' with another string (like 'Score') but that just makes the label even longer, thus requiring a wider (or taller) chart. I'd like the dimension label simply to read 10%, 20%, 30%, .... 90%, 100%. No scores will ever be over 100% so that last dimension label can be misleading for my users. Of the two issues this is the more important of the two. I know I could hard-code this logic into the load script but that limits the flexibilty of design on the front-end. I could no longer allow users to specify the width of the bins (perhaps they want to show those who score <= 20%, <= 40%, <= 60%, <= 80%, <= 100% instead) or maybe they want to add some sort of adjustment factor to the (Numerator/Denominator) expression. If this is done in the load script the user can no longer make those changes. I'd like to remain as flexible as possible here.

2. Since not all scores are represented in the data set I end up with "holes" in my chart. As in the example above everyone scored at 70% or higher. Therefore, my dimension axis has no labels for 0 <= x < 10 through 60 <= x < =70. I'd like to show these. I have a suspicion that a data island / disconnected dimension will be suggested as a solution here but as we know that usually involves pretty poor performance with large data sets (See post titled "How to display zero.missing values"). I was wondering if there is a way around that.

Any thoughts on this would be greatly appreciated. Thanks!

Chris

[kuba_michalik]

For the dimension label beautification, have you considered cutting up the results of Class() and rearranging/omitting parts with string functions? It's a calculated dimension already, so I doubt some mix&match with strings would impact performance significantly.

As to the holes in the chart, you could create an expression that will for sure return something for every dimension value (expressions based on the same field as the dimension work well), even if there are no actual data linked to that dimension. Then disable all presentation options (Bar, Line etc.) in the chart for that expression. The expression will be completely invisible, but it will still make all dimension values appear. Of course that assumes that there are appropriate dimension values somewhere in the model. It's hard to say if this should be possible without knowing how the data are actually structured.

[#3]

Thanks - the string functions applied to the output of class did allow me to make up a "nice" label. I was wondering if there was some other function (or method of constructing my chart) that would do this automatically, but this works well enough!

For the holes in the chart piece - my dimension in this chart is the output of the class function applied to the expression "Numerator / Denominator". Numerator and Denominator are both fields in my data model but I'm not sure how I would construct an expression using either of those that would fill in the missing holes. Would you be able to elaborate on this at all? If it is helpful I could try to put up a scrambled version of my app so you could see it. Unfortunately it has patient-related information in it which I cannot release due to US HIPAA regulations.

Chris

[kuba_michalik · Accepted solution]

Basically, first you would have to make sure that across all data, there is at least one combination of Numerator and Denominator that will fall under each and every of the dimension values you want to show. If there is not, this method will not work (but you could add values in Numerator and Denominator fields that will have no other data associated - whether you can do this depends on what else you do with those fields, and it's hard to answer in general if this is appropriate).

Then you add Numerator/Denominator as chart expression - seeing as it is practically the same expression as the dimension, it is guaranteed to show up for each and every dimension value (so, no holes). You can also guarantee that the values across the whole model, disregarding current selections, will be taken into account by using set analysis (for this expression only) - something like max({1}Numerator/Denominator) Then you hide this expression, so it does not influence the graph layout, but dimension values where this expression is not missing (so, each and every dimension value) will still show up even if all other expressions return missing for this dimension value.

I usually do this with date dimension - sometimes there is no data for a given date, but constructing an expression based on the date itself, I can show all dates in the chart (assuming I have a complete calendar somewhere in the model)

[#5]

Thanks for the further explanation. I don't have data in my application that will allow me to do this but I can definitely see it being useful for future applications. Thanks again!

Chris