topic Why can't I suppress 0 values in my pivot table in QlikView

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 09:35:50 GMT

Hello,

My demo xls is the (fake) output of my pivot table. I don't want to show Mrs Blue and Mrs Purple since they have 0 targets, however, I can't find out where to put the code to hide them.

The 'suppress zero option' doesn't work.

Help is appreciated, thank you!

Wouter Hollander

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 09:47:06 GMT

Hi Wouter,

only a simple question: did you uncheck at the presentation tab (for all dims in your chart) : "Show all Values"

Regards, Roland

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 10:30:17 GMT

Hi Roland,

I can't select this option. I have added a screendump...

Thanks

Wouter

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 12:52:22 GMT

I used your excel sample and replaced field [1], [2] and [3] this way


LOAD am_ci,
 Periode,
 replace([1],0,Null()) AS [1],
 replace([2],0,Null()) AS [2],
 replace([3],0,Null()) AS [3],
 Total
FROM

The zero values are replaced by NULL values.

When building a chart with dimensions am_ci and Periode and expressions sum([1]), sum([2]), sum([3]), sum(Total), the lines for Mrs Blue and Mrs Purple are not shown.

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 14:38:11 GMT

Hi Mark,

Thanks for the answer, I have played with it and I can change the values from 0 to null, however I don't get them to suppress.

When I created a test file to add here it suddenly did work.

So, my feeling is, that I can't do this because of the other values that are present in the data. I have one BIG fact table, and besides 'OMZET' and ' TARGET' there are other values present there as well.

Attached a picture of my datamodel. Can you think of another solution?? Thanks in advance

Wouter

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 16:05:48 GMT

Hi Wouter,

sorry, what I did mean in my previous post was the dimension tab. See attached bit map. If it not as easy as this, don't hesitate to post a little sample app.

Why can't I suppress 0 values in my pivot table

Thu, 18 Nov 2010 16:13:57 GMT

Hi again,

I am in trouble with uploading the bitmap. Next try.....

Why can't I suppress 0 values in my pivot table

Mon, 22 Nov 2010 12:51:16 GMT

Hi Roland and Mark,

Thanks for trying to help.

Yes, I have all unchecked 'Show all values' for all dimensions.

I think it is because in the datamodel I have other values in that specific line. (Which I don't select).

Any other ideas? Or should I really try to make an example QVW ? (is difficult because of not being able to use real data of course).

AW:Re: Why can't I suppress 0 values in my pivot table

brenner_martina — Mon, 22 Nov 2010 13:06:41 GMT

Hi,

if you load data from Excel, there is a problem in recognition of NULL-cells, because Excel is sometimes storing some (unknown) information in empty cells. Thats why your data are not really NULL. I recommend to load only data with values:

Load field1, field2,field3,... from ...excel.xls ... where field1<>0 or field2<>0, or field3<>'' and so on

AW:Re: Why can't I suppress 0 values in my pivot table

Mon, 22 Nov 2010 13:15:22 GMT

Hi Martina,

Thank you for thinking along.

However, it is loaded from a DWH. (excel file was only example).

Moreover, I only read targets with an amount. And the people and departments which are visible, don't have targets. So, they are visible in the lay-out because of other data, which is not used in the pivottable.

Well, I am curious what I can do..

Thanks!

Wouter

AW:Re: AW:Re: Why can't I suppress 0 values in my pivot table

brenner_martina — Mon, 22 Nov 2010 14:15:08 GMT

Hi, Wouter,

there is a new idea, enter dynamic dimensions like:

=If(field1<>'',field1)

if(Sum(Field2)<>0,Field1)

AW:Re: AW:Re: Why can't I suppress 0 values in my pivot table

Mon, 22 Nov 2010 16:15:36 GMT

Okay,

I have worked on a demo, which demonstrates the problem.

In this model I have added the 'visit count' in the overview. However, I don't want this. Unfortunately, the fact that the value there is not null or zero, creates the situation that the zero values don't disappear.

I can't only fix the visit_count, because I have many other items (which I have blanked out in my testdata).

I hope this makes it more clear?

greetings,

Wouter

AW:Re: AW:Re: Why can't I suppress 0 values in my pivot table

johnw — Tue, 23 Nov 2010 00:25:59 GMT

OK, so you want to suppress the row for AM_GROUP if there is no TARGET data for that AM_GROUP? If so, you could replace dimension AM_GROUP with this:

=aggr(if(sum(TARGET),AM_GROUP),AM_GROUP)

Unfortunately, that breaks the ability to navigate back up through the group. So I created a second chart with nothing but the dimension AM_GROUP and removed the caption, giving me the icon and the current field for the group and nothing else. I placed this on top of the original chart where it appears to be the label for the column. Might work well enough.

See attached.

AW:Re: AW:Re: Why can't I suppress 0 values in my pivot table

Tue, 23 Nov 2010 10:45:38 GMT

There we go. That was a difficult one. But you solved it. With the additional chart, it works very well!!

thank you for taking the time to work out this solution!

Greetings,

Wouter