Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Chart Data

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

View solution in original post

9 Replies
Miguel_Angel_Baeyens

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.

Not applicable
Author

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))

Not applicable
Author

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.

Not applicable
Author

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.

Miguel_Angel_Baeyens

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.

Not applicable
Author

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).

Miguel_Angel_Baeyens

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.

Not applicable
Author

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?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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.