Pivot table data setup
Posted by Scott on March 16, 2001 10:27 AM
Hi. I am new to pivot tables, and am having problems setting up what seems like it should be an ideal use for one.
My data is hourly web hits for different days. In other words,
Sunday Mar 1
12 am 5
1 am 6
2 am 8
etc until 11pm on Sunday
Monday Mar 2
12 am 18
1 am 20
2 am 7
Etc until 11pm on Monday
This data extends for a month or more. My goal is to create a pivot table to be able to track web hits on all Mondays, or every night at 11pm, etc.
My problem is that when I use the pivot wizard in Excel for Win 95 v 7 it creates a jumble. My guess is that I don't have my data arranged correctly, but I'm not sure how to arrange it. I could certainly be making other mistakes in concept or execution.
Thanks for any help.
Scott
Posted by keekee on March 16, 2001 10:54 AM
I would set the data up like this:
time Date1 date2 date3 .......
12-8 24 24 42
8-4 34 12 45
4-12 23 23 23
then in the pivot table you can put time or any dates in the row and the sum of date(s) in the data
Posted by Scott on March 16, 2001 11:04 AM
Thank you very much. How would you distinguish between and/or use dates and days?
Should it just be
Time Mar 1 Mar 2 Mar 3
12am 4 6 7
1am 5 7 5
2am 8 9 9
etc
or is there some way to integrate the day-of-week?
Scott
Posted by Mark W. on March 16, 2001 11:59 AM
Scott, to enjoy the full benefits of a PivotTable
your data should be in 1st Normal Form (1NF).
This means no repeating groups. It was suggested
elsewhere that you arrange your data like...
Time,3/1,3/2,....
12:00AM,5,18...
This arrangement includes a repeating group of
hits (5,18, etc.) that is indexed by datevalues.
As you've already surmised this layout would
frustrate your attempts to summarize by any
date attribute whether it by day, year, etc.
To overcome this problem and to reap the
maximum benefits of a PivotTable your data
should be organized as follows:
Date,Time,Hits
3/1,12:00 AM,5
3/2,12:00 AM,18
...
This gives you the flexiblity to include additional
dimensions (e.g., day, year) merely by adding another
column as I have done below:
Date,Time,Hits,Day
3/1,12:00 AM,5,Mon
3/2,12:00 AM,18,Tue
...
