Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 14th 06, 09:49 AM posted to microsoft.public.excel.misc
Todd1
 
Posts: n/a
Default Pivot tables with zero/null values

I have a pivot table that hits a database each day that shows daily
transaction activity. The activity could have values from zero to several
hundred. I want to graph the activity by day. How do I get a pivot table to
show ZERO if there was no activity? Right now, it is null and therefor skips
the day with no activity and the graph never shows the zero amount. See
example below:

Current (not what I'm trying to do)
3/1 17
3/2 4
3/4 72
3/5 21

Desired
3/1 17
3/2 4
3/3 0
3/4 72
3/5 21

  #2   Report Post  
Old March 14th 06, 01:57 PM posted to microsoft.public.excel.misc
Debra Dalgleish
 
Posts: n/a
Default Pivot tables with zero/null values

Group the date field by Days, and turn on the option to Show items with
no data.

Todd1 wrote:
I have a pivot table that hits a database each day that shows daily
transaction activity. The activity could have values from zero to several
hundred. I want to graph the activity by day. How do I get a pivot table to
show ZERO if there was no activity? Right now, it is null and therefor skips
the day with no activity and the graph never shows the zero amount. See
example below:

Current (not what I'm trying to do)
3/1 17
3/2 4
3/4 72
3/5 21

Desired
3/1 17
3/2 4
3/3 0
3/4 72
3/5 21



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Old March 27th 06, 09:39 PM posted to microsoft.public.excel.misc
Todd1
 
Posts: n/a
Default Pivot tables with zero/null values

Debra:

When I do this, all the dates show up as 2-Jan 3-Jan 4-Jan. I try
formatting the numbers and they will not format to "Wednesday, January 2".
Any suggestions?

"Debra Dalgleish" wrote:

Group the date field by Days, and turn on the option to Show items with
no data.

Todd1 wrote:
I have a pivot table that hits a database each day that shows daily
transaction activity. The activity could have values from zero to several
hundred. I want to graph the activity by day. How do I get a pivot table to
show ZERO if there was no activity? Right now, it is null and therefor skips
the day with no activity and the graph never shows the zero amount. See
example below:

Current (not what I'm trying to do)
3/1 17
3/2 4
3/4 72
3/5 21

Desired
3/1 17
3/2 4
3/3 0
3/4 72
3/5 21



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #4   Report Post  
Old March 28th 06, 11:49 AM posted to microsoft.public.excel.misc
David Biddulph
 
Posts: n/a
Default Pivot tables with zero/null values

"Todd1" wrote in message
...
Debra:

When I do this, all the dates show up as 2-Jan 3-Jan 4-Jan. I try
formatting the numbers and they will not format to "Wednesday, January 2".
Any suggestions?


Format Cells/ Custom:
dddd, mmmm d
--
David Biddulph


  #5   Report Post  
Old March 28th 06, 12:45 PM posted to microsoft.public.excel.misc
Todd1
 
Posts: n/a
Default Pivot tables with zero/null values

It will not change. I try formating the column, and the pivot table and
cannot get it to move off the 3-jan

"David Biddulph" wrote:

"Todd1" wrote in message
...
Debra:

When I do this, all the dates show up as 2-Jan 3-Jan 4-Jan. I try
formatting the numbers and they will not format to "Wednesday, January 2".
Any suggestions?


Format Cells/ Custom:
dddd, mmmm d
--
David Biddulph





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables dipsy Excel Worksheet Functions 0 August 12th 05 03:43 PM
Pivot tables - inserting columns Scott Excel Worksheet Functions 1 March 19th 05 01:07 AM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 09:23 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 01:03 AM
Pivot Tables - Variance and % Variance fields CraigS Excel Discussion (Misc queries) 5 January 6th 05 12:22 AM


All times are GMT +1. The time now is 09:55 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017