4 Replies Latest reply on Apr 29, 2015 8:54 PM by ZaraRose

    Line Chart- Sum by week/ month from daily table

    ZaraRose

      Title

      Line Chart- Sum by week/ month from daily table

      Post

      Hi all

       

      My database has a URL table the has 3 fields url , daily unique visitors, date.

      I'm trying to have a history line charts for the data that display daily/weekly /monthly unique visitors

      I'm having problem with calculating the weekly ( the last 26 weeks) visitors for each url

      and the Monthly  visitors ( Last 12 Months) 

      does anyone have any suggestion on how to accomplish this ? 

        • 1. Re: Line Chart- Sum by week/ month from daily table
          philmodjunk

          For monthly sub totals add this calculation field, cmonth:

          Date_Created - Day ( Date_Created ) + 1

          and select Date as the result type. This computes a date for the first day of the month for all dates in that month. If you sort on this field, you group records by month and you can use a summary field for your Y-series to get a single data point for each month in your current found set.

          The same trick works for weekly, but you compute a date for Sunday of each week:

          cWeek:

          Date_Created - DayOfWeek ( Date_Created ) + 1

          • 2. Re: Line Chart- Sum by week/ month from daily table
            ZaraRose

            Thank you Phil,

            But I'm new to filemaker, can you explain how to create the summery field in details because I tried and it did nor work.  

             

            • 3. Re: Line Chart- Sum by week/ month from daily table
              philmodjunk

              Go to manage | database | fields.

              Enter a name for the new field.

              Select Summary as the field type.

              In the dialog that opens text select the options to make this a field that computes the total of the daily unique count field.

              In your chart setup, select this summary field as the Y-series value.

              Be sure that the chart's data source options are for the "found set", "summarized data" option (I'm not recalling the exact names here.

              Then sort your records by the cMonthfield to chart monthly sub totals and sort it by the cWeek field to chart weekly subtotals on your chart.

              • 4. Re: Line Chart- Sum by week/ month from daily table
                ZaraRose

                Thank very much Phil. it works!

                I was sorting data in the table itself not in chart setup which did work. I fixed that and now every thing works perfectly.

                Thank you again!