7 Replies Latest reply on Jan 7, 2017 9:47 AM by philmodjunk

    How to count records for every week/month




      I need some assistance  with creating a Chart on my filmmaker application. I have an time-recording table in my database.


      I would like to have an chart showing the number of activities per week added per week, or per month, or per year.


      I believe i need a way of counting record in a week or month or a year. What is the best approach to have some sort of structure of splitting the data in different time periods; than displaying them on chart.


      Many Thanks in Advance.

        • 1. Re: How to count records for every week/month

          You can set up calculation fields to use for grouping your records by Year, Month and week:


          Year ( DateField ) will return just the year

          DateField - DayOfWeek ( DateField ) + 1 Will return the date for Sunday for every day of that week. (I am assuming that you want a "week" to start on Sunday and end on Saturday.)

          DateField - Day ( DateField ) + 1 will return the date for the first day of the month for every day of that month.


          Using these you can either sort on one of these fields to group them by that common value and use a summary field to get your totals for that time period or you can use a relationship to match to all records with the same year, month or week.

          • 2. Re: How to count records for every week/month


            I would recommend that you create a SUMMARY table of the count of activities per time period. You could create one table that holds the static count of any time period you want.


            This is a bit more complex because you have to create a script that goes to your recording table, finds a certain time period, finds the total of activities for that period, then writes the information to the Summary table.


            I'd make a summary table with these fields: TimePeriod, DateStart, DateEnd, TotalActivitiesCount.


            I'm a fan of storing summary data in tables since last month's total activities won't change, you might as well store it. Then the chart has the simple task of reading from static fields rather than calculated fields.


            If your chart is on a Table occurrence of the summary table, you can do a find for any kind of time period (week, month, year, etc) and the values will display on the chart. You can even find for a certain date range and time period (all the weeks between 1/1/2016 and 4/1/2016, for example) and display those results on the same table.

            • 3. Re: How to count records for every week/month

              I have created few calculations fields in my table.


              -  One for count

              - One for to put month and year together.

              And One to put total record of that month/year.


              It works fine; the only problem is it doesn't count up number of total records in that time period set.

              Please see attached demo file.

              • 4. Re: How to count records for every week/month

                Note that the calculations that I suggested (with the exception of the Year function) compute dates. These are automatically specific to either a particular month and year or to a particular week and year and will sort correctly for any type of chronological sorting that you might want to do.


                Neither they nor yours will by themselves count up any records. But given such values in your table,  you have a variety of methods that you can use that require such fields in order to get those counts.


                You can find a set of records, sort them by the Month field to group the records by month, sorted in ascending order and then a 'count of' summary field can return the number of records in each month. For a chart, you would refer to this summary field as your y-series value and specify the "summarized records" data source option.

                • 5. Re: How to count records for every week/month

                  You were actually pretty close. I modified the "by month" layout to show you what is possible.


                  I set it up as a summary report--something you already nearly had working, and put a chart of the data in the header.


                  The sub summary part is "sorted by" your month_year field. I could have used the new field that I added for comparison, cMonth, but yours works also. I put cMonth in the sub summary part as a way to show one of this calculation field's advantages--I can use date formatting on the field to get a nice field name, year label for each group of records.


                  The chart uses your summary field, but a "count of" summary field would work as well and would not need the "one" field in order to get a count so using it would reduce the number of fields in your table.

                  • 6. Re: How to count records for every week/month

                    Hello Philmodjunk


                    Thank you for the help. It works great.


                    I believe in order to display chart; the data on the chart and layout table must be same.


                    I was now wanting to display same chart on Dashboard. Dashboard layout is assigned to dashboard table and time recording layout is linked with time recording table. BUT both of these tables are related.


                    The chart works great on time recording layout, but if i display the chart on dashboard layout and use the field from time recording table (as these both tables are related); it sort of breaks the chart.


                    So does the chart will only work as long its data and layout are assigned to same table or it can work on different layout as long there is connection between layout table and chart table.


                    Many Thanks.

                    • 7. Re: How to count records for every week/month

                      One small distinction between what you just posted and what is actually the case:


                      The Chart draws its data from the current foundset, not the table. If you perform a find for just certain records, your chart will update to show bars based only on those records that are in the found set.


                      Some chart objects can display data from a related table. This type, which uses the "summarized data" data source option, cannot as it relies on the current found set's records and sort order to get correct values from the summary field that is used in the Y-series.


                      An earlier post suggested creating a summary table. If you created such a table, you could chart from that via a relationship to the data as this option would have one record for each group of records in your current table and thus you would not need to refer to a summary field for the Y-series data.


                      Another option is that you can use GetLayoutObjectAttribute to get a bitmapped image of the chart. If you use this function with set field, you can copy the image into a container field. Thus, it is possible to run a script that goes to the layout where I set up the chart, finds and sorts records as needed and then sets a container field to the chart image. This container field could then be placed on your dashboard layout. a "refresh" button on the dashboard could be used to run such a script.