My problem isn't from a business or financial background, but for data analysis for scientific research. I'm using two different sources of exported data from different programs counting two different animal behaviors that I ultimately want to plot by hour by day on a very long graph. I'm going to try and leave out any unnecessary details on the data sources, etc., to try and make my issues as simple as I can.
This forum has been very useful on learning how to use PivotTables and converting timestamps to useable date and time information. I am generating nice PivotTables in no time at all now!
The problems I am encountering in making by "by date by hour," graph are:
The exported data places some limitations on how I can organize the data.
One program's exported data, when put into the PivotTable has zero values for hours that don't have a counted behavior (so it reads the date, then 12 AM through 11 PM are the rows, regardless if there is meaningful data or not). The column of interest is also calculated by using "Sums of COUNTS" since the data collection program already counts each behavior. My PivotTable allows me to group occurrences by hour. I use some work-arounds to get the useful data in columns of DATE, TIME and COUNTS.
The other program only lists timestamps when the behavior occurs, and nothing when there is no behavior. I use this PivotTable to count the behaviors and group by hour. However, it only lists the hour when something happened, say for "5/30/11" only 5 AM and 11PM are listed, while the other hour labels are not. The work-arounds on the exported data only give me DATE and TIME.
I don't know how to use the "Multiple Source Consolidated Pivot Table," but as far as I understand them, they wouldn't work since the two sources of data are too different. (But maybe!)
I think the solution would be (thought I don't know how to accomplish them)
-Get the two Pivot Tables to play nice
-Somehow expand out the missing hours for the PivotTable that only shows positive results. I did this by hand, and posted the two types of behavior in adjacent columns to make the chart I wanted but it took a very long time.
Again, I'm trying to spare you all some of the excruciating details and get down to the real core of the problem, but I'd happily provide any kind of detail you think you'd need. There may be a more elegant solution, but help with the problem in bold print would be a life-saver. Thanks in advance for your help and sorry for potential confusion!
This forum has been very useful on learning how to use PivotTables and converting timestamps to useable date and time information. I am generating nice PivotTables in no time at all now!
The problems I am encountering in making by "by date by hour," graph are:
The exported data places some limitations on how I can organize the data.
One program's exported data, when put into the PivotTable has zero values for hours that don't have a counted behavior (so it reads the date, then 12 AM through 11 PM are the rows, regardless if there is meaningful data or not). The column of interest is also calculated by using "Sums of COUNTS" since the data collection program already counts each behavior. My PivotTable allows me to group occurrences by hour. I use some work-arounds to get the useful data in columns of DATE, TIME and COUNTS.
The other program only lists timestamps when the behavior occurs, and nothing when there is no behavior. I use this PivotTable to count the behaviors and group by hour. However, it only lists the hour when something happened, say for "5/30/11" only 5 AM and 11PM are listed, while the other hour labels are not. The work-arounds on the exported data only give me DATE and TIME.
I don't know how to use the "Multiple Source Consolidated Pivot Table," but as far as I understand them, they wouldn't work since the two sources of data are too different. (But maybe!)
I think the solution would be (thought I don't know how to accomplish them)
-Get the two Pivot Tables to play nice
-Somehow expand out the missing hours for the PivotTable that only shows positive results. I did this by hand, and posted the two types of behavior in adjacent columns to make the chart I wanted but it took a very long time.
Again, I'm trying to spare you all some of the excruciating details and get down to the real core of the problem, but I'd happily provide any kind of detail you think you'd need. There may be a more elegant solution, but help with the problem in bold print would be a life-saver. Thanks in advance for your help and sorry for potential confusion!
