Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Does Not Show Data
I have a pivot table in Excel based on data I have in Access. (It's easier to
see.) The data is information that is categorized by different times of day. In the PT,I have the times in the Row and Grouped by hour. Then a count in values. My issue - if I there is no data for a particular time of day, the Pivot will not put a '0', it will just not show any information for that time. How can I tell the Pivot if there is no info for this hour, show 0? I googled and found info about Data Validation - if thats the answer, how do I use it? Example Hour 12 ---3 1 ----4 2-----5 3-----10 4-----11 *If there were no calls at 3, it would not show 3 at all. it would skip over that row. How do I make it show '0' if when there is nothing there? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Does Not Show Data
Usually in this circumstance, I'll force all of the times of day to show up
by appending "dummy" records to my table in Access. So you can have a table with 24 records in it - one for each hour of the day and just append those dummy records to your table. Then you'll be sure that each hour will show up! For a little more elegant (and challenging!) solution, you could also do it by using a Union query in Access and Unioning the dummy record table into your original table on the fly. Now the problem becomes that you have an extra record for each hour. You can fix that in your other formulas or analysis by just always subtracting 1 from the result. Alternatively, you could have a field in your database (let's call it RowCount) with a 1 in it for each record that you have. For each of your "Dummy" records, you'll have RowCount = 0. Then, when you pivot, you can SUM RowCount instead of COUNTing the records and voila! you've got a good count with all of the times represented. Happy calculating! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Does Not Show Data
Thanks. this is great I will give it a try.
"GSnyder" wrote: Usually in this circumstance, I'll force all of the times of day to show up by appending "dummy" records to my table in Access. So you can have a table with 24 records in it - one for each hour of the day and just append those dummy records to your table. Then you'll be sure that each hour will show up! For a little more elegant (and challenging!) solution, you could also do it by using a Union query in Access and Unioning the dummy record table into your original table on the fly. Now the problem becomes that you have an extra record for each hour. You can fix that in your other formulas or analysis by just always subtracting 1 from the result. Alternatively, you could have a field in your database (let's call it RowCount) with a 1 in it for each record that you have. For each of your "Dummy" records, you'll have RowCount = 0. Then, when you pivot, you can SUM RowCount instead of COUNTing the records and voila! you've got a good count with all of the times represented. Happy calculating! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show Pivot Table Items That Have No Data | Excel Discussion (Misc queries) | |||
Pivot Table- Show Data % of | Excel Discussion (Misc queries) | |||
Show all data in pivot table | Excel Discussion (Misc queries) | |||
How to show data greater than 10 in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table - show items with no data | Excel Discussion (Misc queries) |