Remove dimension values if count observations < 100

[#1]

Hello all,

I have a trend analyse in my application. A bookyear is showing average scores.

Customer only wants to see a month if the # of observations > 100.

I succeeded showing an average of 0 if the nbr of observations < 100, but they would like to remove the complete month from the line graph in this case.

I tried to do something with Suppress zero values, but did not succeed yet.

Any suggestions?

Thanks!

Best regards, Patricia

[martin59]

Hello Patricia,

You have to create 2 charts and put a conditionnal showing on both. In your condition, you could count your number of months.

Hope it helps you

[lironbaram]

hei

attach is an example

you need to do the condition on the dimension

i find it confusing ,but the customer all-ways right

hope it helps you

[#4]

Works perfectly, thanks for your help!

[#5]

It worked perfect, but now they changed their minds.

They would like to see the months, but no line (no score) for the months where nbr of observations < 100.

So, they would like to have holes/gaps in the line.

Is this also possible?

Best regards!

[johnw]

patricia.weemer wrote:
They would like to see the months, but no line (no score) for the months where nbr of observations < 100.
So, they would like to have holes/gaps in the line.

Ah, now things get a little complicated because QlikView hates showing you missing data. 🙂

If there is any chance that you won't have every month, generate a table on the side to make CERTAIN you will:

Months:
LOAD recno() as month
AUTOGENERATE 12
;

Then set up your chart like this:

Dimension = month // show all vlaues
Expression = if(sum(Obser)>100,sum(Obser),0)

On the presentation tab, uncheck suppress zero-values, uncheck suppress missing.
On the axes tab, checkmark continuous, set static min to min(Month), static max to max(Month).

See attached.

Now, it doesn't completely remove the line segments when they're zero, but it does drop to zero. I thought I could remove them by coloring them white, and I can, but it ends up removing the symbols on the month prior to it dropping to zero, and if the month before and the month after are zero, that means you won't see that data point even though it has a value. So it didn't work well, but for the sake of showing what I'm talking about, I colored the segments I wanted to remove red, and the other segments blue. You can see the red dots that would disappear if I used white. The expression is this:

if(sum(Obser)>100 and above(sum(Obser),-1)>100,blue(),red())

And to find it, click on the little + next to the column expression. Then click on "background color". That's where it's hiding. Doesn't work, but maybe someone can think of a way to make it work if you really do want these segments to disappear.

Bar charts work fine since there's no line connecting the points.

[#7]

Thank you very much for your help!

I understand what you are trying to do and I tried to do the same (did not succeed yet). But before I continue...I have another dimension in this chart, Product. So for each Product I have 1 line. And of course they all have a different color.

So, I do not think it is a solution for multiple lines. What do you think?

[#8]

Or maybe there are other alternatives. I have no idea how, but a text in the graph if 1 product has < 100 observations. This is a question of the customer, but I cannot see how to do it if you have multiple lines.

But maybe someone has a good idea or an example how this can be solved????

[lironbaram]

hei petricia

as mentioned before

attach is an example for possible solution

what i did is changing the line style

so if a month get a value of 0 the line is dashed

so you can say when its a real line and when not

hope its helps you

[johnw]

I don't have a two-dimension solution working yet. But I thought I'd post what seems to be a working example with one dimension. I simplified the expression to a simple sum(Value) for demonstration purposes. It plots points only if > 0, and plots line segments only if both end points are > 0. I think that's what we're looking for, though again, this approach only works for a single dimension.