Ignore zero values in pivot chart

cazzzac

New Member
Joined
Jul 30, 2012
Messages
19
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Jul 30, 2012
Messages
19
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Jul 30, 2012
Messages
19
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Jul 30, 2012
Messages
19
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Jul 30, 2012
Messages
19
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
try to put the month under the name in the axis field... would that be closer to what you need?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,109
Messages
5,466,716
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top