Ignore zero values in pivot chart

[cazzzac]

Hi there,

Background:
I have a data set for a call centre that displays each staff member's daily call numbers across two different call types (Call Type 1, Call Type 2) - staff only work on one particular call type each day. I would like to create two pivot charts, one with daily/weekly/monthly averages for Call Type 1, and one for Call Type 2.

Problem:
On the Call Type 1 pivot chart, is there any way to exclude staff members who only work on Call Type 2 by ignoring zero values and not displaying their name on the horizontal axis? Currently, if a staff member was working on Call Type 2, they will still display in the Call Type 1 chart as a gap. I have read in other forums that you can set a minimum value on standard Excel charts, but I can't see how to do this on a Pivot Chart.

Cheers,

Zac

 

[cyrilbrd]

Hi,
Is your zero manually entered or is it the result of a formula?
Where is it showing in your pivot, in the ∑Values? or in the Row Labels?

 

[cazzzac]

Hi,
Is your zero manually entered or is it the result of a formula?
Where is it showing in your pivot, in the ∑Values? or in the Row Labels?

Hi cyrilbrd,

The zero is the result of a formula in the data set. I have some fields with zero in there, and some with n/a.

In the chart, i have:
Employee name in the Axis Fields
Month in the Legend Fields
and Average of daily stats in the Values field

If the staff member didn't complete any work in Call Type 1, for example, there will be a zero value in 'Average of daily stats' in the Values field. So we have some staff members that have Apr/May/Jun stats with zero values clogging up the chart.

 

[cyrilbrd]

Have you tried to select the Column Labels filter, the select Value Filters, Greater than and put 1. Thus would eliminate all averages than a zero.

 

[cazzzac]

You mean filtering the columns in the data set? I tried this on one of the columns in the data set, but it didn't work - the person's name still showed up in the chart with empty data.
Perhaps its just a matter of manually filtering the person's name on the pivot chart, but I was hoping this could be automated somehow.

 

[cyrilbrd]

mmmh, this is what I have :
a report filter
a column Labels
a Rows Labels
and a ∑ Values.

Please confirm that you have your average of daily stats is in the ∑ Values and the other fields in the rows Labels or a combination of rows and column.

 

[cazzzac]

I have:
A report filter - nothing in this field
Axis Fields (rows) - Employee name
Legend Fields (columns) - Month
Values - Average of daily stats.

I can't see any column label filters in the pivot chart - not sure if a pivot chart works differently to a standard excel chart in this regard.

The chart currently looks like this, with the blank space being the employee with zero values.

 

[cyrilbrd]

So the problem is that you want the columns to be closer to each other whenever the value is zero. The concern is that you would like to show ONLY the data that are not zero (hence without gap in between the columns). Is this correct?
Sorry for not understanding your concern right away. I may not have a solution as the pivot chart will divide the columns per Axis Filed and Legends Fields (Or Column and Rows Labels in the adjoined Pivot).
Maybe working outside the pivot might give you a less disparate chart...

 

[cazzzac]

No worries. Yes, it's looking like i'll have to maybe create some specific tables for each Call Type, then build my charts off that, rather than filtering from a master data set. Thanks for trying anyway

 

[cyrilbrd]

try to put the month under the name in the axis field... would that be closer to what you need?