Reference Line Label for a Scatter Plot

[#1]

Hi All,

I have a scatter plot where if certain conditions are met for a record, two variables, "score" and "balance" (both are continuous variables) are plotted against each other. The reference line is showing the median on the x-axis. The defintion of the reference line is Percentile = 50%.

The issue I am having is with the label - I want the label of the reference line to display the value of the median of the x values for each record. So, if I have 5 records with the following (x, y) coordinates, (5, 100), (2, 125), (9, 100), (9, 125) and (9, 75), I would expect the label for the reference line to display "9" when I enter "=median(score)" as the label equation.

However, for the x values, QlikView is regarding them as: 2, 5 and 9...not: 2, 5, 9, 9 and 9 and is therefore incorrectly displaying the value for the reference label as "5".

Any thoughts?

[#2]

Could you set up a sample file that demonstrates this problem? I just tried to duplicate it and was unable. I Inlined your x values and the median result comes out as 9. I even made a Chart with a Reference Line set to the label of: "=Median(Num)" and it displays a 9.

I'm thinking there is something in your app affecting this calculation.

[#3]

Thanks - by providing an example of what I am talking about, it's helped me to be more confused ! With making a much smaller example of the original dataset, I think my above example and explanation is a bit off. So, please take a look at the attached QV file and let me know what you think.

Ultimately, I need to be able to show a reference line and label that reflects the values displayed in the data set. I want to filter that data in the expression equations as I have done so that the end users do not have to select "1" in the "Active" field. In order for the reference line and label to show correctly (which in this example should be "6.5" for "Score"), I need to have the following equation:

median(if(sum(Active>0),Score))

However, when I try to do so, the message "// Error in expression: Nested aggregation not allowed" shows up (when I put the above equation in the label expression for the reference line, it displays the message in the scatter plot).

Also, if I were to have "=median(Score)" for the reference line and label expressions, then click on "1" for the "Active" field, the line and label both display correctly with the value of "6.5", but as I mentioned before, I don't want the end user to have to filter the data themselves (plus I want the same report to display different scatter plots with different cuts of data, like when Active = 0).

Let me know what you think and thanks again for the help!

[#4 · Accepted solution]

Oh! Solved it...

Since there cannot be a nested aggregation (I believe using the sum() and median() in the same formula...maybe someone can expand upon this) with the above formula, the following would apply using the "aggr" function:

=median(if(aggr(sum(Active>0),Acct),Score))

When the above formula is entered in the reference line and label expressions, the median is properly displayed as "6.5".