8 Replies Latest reply on Jun 1, 2011 1:10 PM by philmodjunk

    bar graph dealing with time sorted by day?

    gasolineman

      Title

      bar graph dealing with time sorted by day?

      Post

      i have a db that has time in a field that mark when a record is created... i want a report that would show me a bar graph (any graph will do) that show me 8 bars representing 8 hours in a day and the amount for each bar would be total for how many records were created during that hour (those hours) how would i go about creating this?  i have tried a couple different times using the layout wizard but cannot get them to show me anything even remotely close to what im looking for.

      suggestions would help!  THANkS!

        • 1. Re: bar graph dealing with time sorted by day?
          philmodjunk

          You can use a number of different calculation fields to give all records created in the same hour a common value. You'll need such a field so that you can group your records properly for you chart.

          You'll also need a summary field that uses the "count of" option to count a field (such as your creation time field) that is never blank.

          Here are some possible calculations you might use:

          ResultType should be TimeStamp:   TimeStampField - Minute ( TimeStampField ) * 60 - Seconds ( TimeStampField )
          Result should be number: Hour ( TimeStampField )
          Result should be number: Hour ( TimeField )
          Result should be Time: Hour ( TimeField ) * 3600

          I suggest using a time stamp field for both your creation time and this calculation as it gives you more options for how you chart your data. (You can chart for a time frame that spans more than a day, for example.)

          Then it's just a matter of setting up your chart with the correct settings:

          Use the calculation field as your X axis. The Summary counting field would be the Y axis. Then Specify Use Data From Current Found Set, Show Data Points for Groups of Records when sorted.

          Then if you find the records you want to chart and sort them by your calculation field, you'll see your chart with totals for each hour.

          • 2. Re: bar graph dealing with time sorted by day?
            gasolineman

            anyone else have anything else they can suggest?

            • 4. Re: bar graph dealing with time sorted by day?
              gasolineman

              the problem that i am encountering when using your template is that the bars in the graph do not combine the number of calls per hour.  meaning... i see an instance in the chart got each record that was in the same hour.  what im hoping to see is a bar that totals the number of records per hour by day.

              http://i.imgur.com/XZ22r.png

              • 5. Re: bar graph dealing with time sorted by day?
                philmodjunk

                There are multiple options here so if I misunderstand, please be patient.

                In the demo file, all records were created on the same day.

                6 were created in the 2pm hour
                1 in the 3 pm hour
                2 in the 4 pm hour
                1 in the 5 pm hour

                Which is what the chart displays. (An easy way to examine the data being charted is to select table view.) Thus, the chart combines the number of calls for each hour where at least one call was made. Is it that the chart does not show a "zero bar" for each hour where no calls where logged? Or....?

                If you need a spot on the chart for every hour even if no calls where made, we'll need to use a different approach. I can think of a way that uses a "charting hours" table where you have one record for each hour of the day, but will wait your response before elaborating on that idea.

                • 6. Re: bar graph dealing with time sorted by day?
                  philmodjunk

                  Just noticed a detail I missed the first time I looked at your screen shot.

                  I see multiple bars with the same hour. Is this what you want or is this a screen shot from the demo to show what you do not want?

                  To limit the demo file's bar chart to just one day, first perform a find for records of just that date, then sort your records by the cHourStamp field. (The records must be sorted in order for the chart to correctly display the data.)

                  In a full up solution, this process can be automated with a script.

                  • 7. Re: bar graph dealing with time sorted by day?
                    gasolineman

                    what i would like is a chart that display in a report, where each day has a chart that displays the number of records that were created within a single hour over the period of the day.  that image i sent is close, but i DONT want a bar for each record, i want one bar that totals the number of records in that one hour

                    • 8. Re: bar graph dealing with time sorted by day?
                      philmodjunk

                      The only reason the demo file is showing one bar per record is that the records are unsorted. If you sort them by cHourStamp, you will see one bar for each hour of the day, not one bar for each record.

                      Here is an updated copy that produces the report you describe, provided the records are correctly sorted. The only changes made were to add a calculation field, cDay that uses GetasDate to extract the day's date from the time stamp field and then I placed the existing chart object inside a sub summary part "when sorted by" cDay. I also modified the chart title to include cDay so that each day's chart is labeled with the date.

                      I did this on a separate layout so that you can compare the original layout to the modified report layout that produces a chart for each day.

                      http://www.4shared.com/file/CzaN6VS1/HourlyBarChart_2.html