topic Conditional Chart Data in QlikView

Conditional Chart Data

Mon, 11 Apr 2011 14:22:22 GMT

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.

Conditional Chart Data

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 14:44:15 GMT

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.

Conditional Chart Data

Mon, 11 Apr 2011 17:50:50 GMT

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?

(Count(Case) < 10, ARGB(0, 0, 0, 0))

Conditional Chart Data

Mon, 11 Apr 2011 17:53:48 GMT

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.

Conditional Chart Data

Mon, 11 Apr 2011 18:17:26 GMT

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.

Conditional Chart Data

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 19:05:01 GMT

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.

Conditional Chart Data

Wed, 13 Apr 2011 20:41:15 GMT

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

Conditional Chart Data

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 22:22:35 GMT

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.

Conditional Chart Data

Fri, 15 Apr 2011 21:29:59 GMT

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

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?

Conditional Chart Data

rwunderlich — Sun, 17 Apr 2011 06:37:09 GMT

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.