Grouping by month

[TVRV8S]

I want to chart how many enquiries we have per month in our Contacts table.

 

Each enquiry has a timestamp when the record was made.

 

Can anyone tell me how I can set up a chart to display the number of enquiries for each of the last 12 months?

 

Thanks.

[dansmith65]

Create 2 fields in the enquiry's table:

• "createdMonth" as a calculated date field, with this calculation:

• Date (
      Month ( createdTimestampField ) ;
      Day ( createdTimestampField ) ;
      Year ( createdTimestampField )
  )

   

• "count" as a summary field that counts a field that will not be empty (like the primary key field)

See if you can figure it out from there

[TVRV8S]

Hi there,

 

Thanks for your help. Doing that creates a count field that just gives me the total number of records in my find, not a count of each record per month.

 

The createdMonth also only gives me the day rather than the month.

 

I want the data to come out thus in the graph:

 

November 2011 - 86

December 2011 - 74

January 2012 - 142

February 2012 - 165

March 2012 - 193

April 2012 - 178

 

etc

 

So I want to group all the records in each month and find out how many there were.

 

Any help much appreciated.

[dansmith65]

The createdMonth isn't a day, but a date - which can be configured to be displayed as only a Month and Year.

 

If you sort the found set by the createdMonth field, then add a subsummary part to your layout and add your count field to it, it will give you the information you need. To make it look like you showed, you would delete the body part from your layout.

 

You mentioned both "chart" and "graph" now, at first I thought you were using a chart to display the data, now I'm wondering if you really want to use a sub-summary report. Whichever method you use to display the data, the principals are the same.

[LaRetta]

Date (  
Month ( createdTimestampField ) ;     
Day ( createdTimestampField ) ;     
Year ( createdTimestampField ))

Hi Dan, you might want to take another look at this calculation because it is not a 'month date' . You mean day should be 1 right?

[dansmith65]

oops!   Thanks for catching that LaRetta, and sorry for confusing you TVRV8S. The calculation was supposed to return the first day of the month the record was created in, which should look like this:

Date (
    Month ( createdTimestampField ) ;
    1 ;
    Year ( createdTimestampField )
)

[TVRV8S]

This is very confusing, unfortunately.

 

Let me run through the steps.

 

I have a Contacts table, the date field for each record is called Created and the primary key is ContactID.

 

So I...

 

1. Add two fields to my Contacts table: CreatedMonth and CreatedSummary

 

CreatedMonth is a Date field, and I specify a calculation of Date(Month ( Created ) ; 1 ; Year ( Created ) )

 

CreatedSummary is a Summary field with the option of a Count of ContactID, with summarist repetitions as Individual

 

2. Create a new layout based on Contacts table

 

3. Delete the Body part and add a subsummary Part sorted by CreatedMonth

 

4. Insert the CreatedSummary field onto the subsummary part.

 

5. Create a new bar chart and set the X axis as CreatedMonth and Y as CreatedSummary

 

--

 

If I do this, all I get is a lot of equal height bars like before.

 

This is my very first venture into charting in Filemaker so any help much appreciated. There doesn't seem to be a decent beginners guide anywhere online.

[dansmith65]

Are you you trying to create a sub-summary report?  Or a chart?

 

For creating a sub-summary report, everything looks good up to and including step 4. Since you're almost there, do that step first, then you'll at least be able to see what's going on. After step 4, add createdMonth field to the subsummary part, find the records you want to report on, sort by createdMonth field.

[TVRV8S]

I'm trying to create a bar chart merely to tell me how many enquiries per month we get.

 

I added the subsummary part because you advised me to above, but I don't really understand what it does or why I would need it in order to create a chart.

[comment]

I'm trying to create a bar chart merely to tell me how many enquiries per month we get.

 

Creating a monthly chart is practically the same thing as creating a monthly summary report - see the attached example.

MonthlyChart.fp7.zip

[TVRV8S]

Many thanks for your help. I think I have it working now.

 

My next question is this:

 

I have an Appointments table and an Agents table.

 

I already have a relationship between Agents (Agent Code) and Appointments (fk_Agent Code) so that I can display all appointments for each agent in a portal in the Agents layout.

 

Each appointment has a Created date field.

 

On the Agents layout, I would like a bar chart showing the number of appointments per month based on what agent I am viewing rather than every agent.

 

Is this possible?

 

I would also like to do the same chart but with sales figures for the agent I am viewing.

 

 

Further help much appreciated.

[comment]

On the Agents layout, I would like a bar chart showing the number of appointments per month based on what agent I am viewing

 

 

That will be slightly more complicated, because Filemaker does not readily sub-summarize the related set - as opposed to the found set, which is very easy to group.

 

The best solution, IMHO, is to use an auxiliary table of Months and populate a global field in this table with the AgentID of the currently viewed agent. This way each Month record can summarize the related records - in Appointments and or in Sales - related only to the current agent.

 

You do need to decide, however, which or how many months to include in this summary - since naturally the number of months for which data is available keeps growing.