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

[#1]

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

[brenner_martina]

Hi, Wouter,

there is a new idea, enter dynamic dimensions like:

=If(field1<>'',field1)

or

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

[#3]

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

[johnw · Accepted solution]

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.

[#5]

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