topic Remove dimension values if count observations < 100 in QlikView

Remove dimension values if count observations < 100

Mon, 10 Jan 2011 11:51:58 GMT

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

Re. :Remove dimension values if count observations < 100

martin59 — Mon, 10 Jan 2011 12:10:26 GMT

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

Remove dimension values if count observations < 100

lironbaram — Mon, 10 Jan 2011 12:44:59 GMT

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

Remove dimension values if count observations < 100

Mon, 10 Jan 2011 14:20:10 GMT

Works perfectly, thanks for your help!

Remove dimension values if count observations < 100

Mon, 10 Jan 2011 17:54:19 GMT

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!

Remove dimension values if count observations < 100

johnw — Tue, 11 Jan 2011 02:25:51 GMT


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.

Remove dimension values if count observations < 100

Tue, 11 Jan 2011 09:22:36 GMT

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?

Remove dimension values if count observations < 100

Tue, 11 Jan 2011 09:42:16 GMT

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

Remove dimension values if count observations < 100

lironbaram — Tue, 11 Jan 2011 10:15:17 GMT

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

Remove dimension values if count observations < 100

johnw — Tue, 18 Jan 2011 02:25:08 GMT

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.

Remove dimension values if count observations < 100

johnw — Tue, 18 Jan 2011 20:38:37 GMT

OK, I have a solution working for two dimensions now. It's rather complicated, including an AsOf table to connect a month to the next month, and two charts carefully lined up on top of each other. The conditional color assigned is no longer blue vs. white, but default color vs. argb(0,255,255,255), which is to say transparent white so that the white line never overlays a colored line. It's possible that there is a simpler solution, but at least it seems to be working, and I wasn't able to push the charts out of sync with at least some brief poking at it.

Remove dimension values if count observations < 100

Fri, 28 Jan 2011 11:34:37 GMT

Hi John,

Sorry, was on vacation and just read your replies. Thank you so much for this, I really appreciate it!

Very clever! I will show this to my customer this afternoon before implementing.

Still one question. Because I cannot see this published yet, do you know how this will be exported to excel and how it will be printed. Because you used 2 charts. Do you have experience with some other examples with 2 charts on top of each other?

Best regards,

Patricia

Remove dimension values if count observations < 100

johnw — Fri, 28 Jan 2011 18:58:07 GMT

There shouldn't be any problem with Excel since both charts contain the same information, just displayed a little differently. So when you export the top chart, you should get what you'd expect. It seems to work. Didn't check all the data, but I do have most months and all products, and there are no zeroes in the Excel, just like there were no zeroes in the chart.

Printing I assume won't work, and will only print the symbols. Yeah, that's what it shows in the print preview, only the symbols, no lines. You'd have to print the sheet instead to see it correctly.