Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've created a line chart where each pathologists monthly average turnover is plotted over time. Since the average calcualtion is misleading if their caseload is low I would like to exclude the data from the chart if it is lower than a specific threshold. How do I conditionally determine if data should be plotted?
My expression used currently is ' Avg(Diff)'. I tried doing a conditional 'If(Count<10){Avg(Diff)}' but that is bad syntax. I also tried messing with the dimentions but had trouble there as well.
I would think you could just exclude the data with
if(count(Case)>10, avg(Diff), 0)
and then suppress zero values on the chart.
But if that's not working for you...Get a copy of the QV Cookbook and look at the example "Removing outlier values from a chart dimension". There might be some useful ideas there for you.
Hello Chad,
Using background colors you can "hide" some plots or bars in line or bar charts. If you don't want to show those values less than 10, then use the following in the Background Color expression
If(Avg(Diff) < 10, ARGB(0, 0, 0, 0), Black())
So if the result of Avg(Diff) for any given value of the dimension is less than 10, it will be rendered as "transparent". This may create some gaps in a line chart.
Hope that helps.
That almost works. For the Line Chart it will hide the line after the data point but will show the line connecting to the point that I do not want displayed. Is there a way I can look forward in time to conditionally hide this part of the line as well?
IF
(Count(Case) < 10, ARGB(0, 0, 0, 0))
Instead of trying to hide the line can I exclude the data point all together? Part of the reason to do this is because an extreem outlier will affect the scale of the chart and if I hide the line it'll still probably keep the same scale.
Now that I'd think about it I need to do it in the load script query, right? I'd rather not because I'd like to show the values in a table, just not in a graph if the data is not relevent.
Hello Chad,
Unless you don't want either show those value nor aggregate them, I'd do all these in the chart.
The value in a line chart is the value itself and the line that goes from this point to the next. You can "hide" (using transparent background) the point and the line before() it. If you don't want to show the value either, then use the conditional in the "Text Color" property of the expression, besides the background color, that affects to the bar/line.
If you are rendering a table instead of a chart, I'd use a set analysis rather than changing the script:
Avg({< CustomerName = {"=Avg(Diff) >= 10"} >} Diff)
CustomerName would be your dimension.
Hope that helps.
I guess I'm still having problems. You mention that the value in a line chart is the value itself and the line that goes from this point to the next. I can hide this line in the background color expression. But then you mention that you can "hide" the point and the line before it and I don't see how without being able to hide it in a similar way with at the previous data point (which would require knowing the value at a future data point).
Hello Chad,
Here you are a little trick. Create two identical expressions, one for the data points, and the other for the lines. Use the conditional background color to show/hide and give that "gap" effect I'm guessing you are looking for. Hope the attached file helps.
Regards.
Well it's a shame that I have the Personal Evaluation edition so I can't open the file. Thanks though. I indeed want the gap effect and can get half of it. Hiding the first part I still don't get.
I'm sure you get my issue but just to be sure this is a crude representation of my problem. Below is a plot with 'X' as the data points and 'o' as the display line. Assume that April is a bad data point because there weren't ernough samples for accurate data.
X
X o o
o X o X o X
o o o o
X X
Jan Feb Mar Apr May Jun Jul
When I do a hide in the Background Color using If(Count(Case) < 10, ARGB(0, 0, 0, 0)) it looks like this
X
X o
o X X o X
o o o
X X
Jan Feb Mar Apr May Jun Jul
When really this is what I expect.
X
o X X o X
o o o
X X
Jan Feb Mar Apr May Jun Jul
Is the identical expressions the way to go?
I would think you could just exclude the data with
if(count(Case)>10, avg(Diff), 0)
and then suppress zero values on the chart.
But if that's not working for you...Get a copy of the QV Cookbook and look at the example "Removing outlier values from a chart dimension". There might be some useful ideas there for you.