Charting new record count by month

[obiejuankenobi]

K, here is another one for the chart guru's.

 

Using FMPro13

 

This seems like it should be really easy but i have tried several similar tutorials i found and tried reverse engineering several sales total charts by month and just cant get it to work.

 

On my [CLIENTUPDATE] table i have these two fields in question:  (Update Date) & (CLIENTUPDATE_ID) 

(CLIENTUPDATE_ID) is the primary key in this table.

 

I am trying to create a line graph that counts the number of primary keys "updates" and charts them by month over the last 12 months.

 

 

Thanks for your help!

 

 

 

 

 

[eos]

You need

 

• a calculated field that serves as a month indicator, say, cUpdateMonthAndYear, defined as Month ( YourTable::updateDate ) & "|" & Year ( YourTable::updateDate ), with result type text

 

• a summary field sCountOf for clientUpdateID

 

• a list layout with a sub-summary-part that uses cUpdateMonthAndYear as break field

 

• a chart object that uses sCountOfClientUpdateID as chart data, and "Found Set" as data source

 

• a script that finds all records for the last 12 months and sorts them by cUpdateMonthAndYear

 

Note that you don't have to display the calculated field, either in the sub-summary parts or in the chart.

 

For the x axis title of the chart, you can use e.g. MonthName ( YourTable::updateDate ) & " " & Year ( YourTable::updateDate ); in the layout, give updateDate field a custom date formatting.

[obiejuankenobi]

Thanks eos,

 

Can you elaborate on the:

• a script that finds all records for the last 12 months and sorts them by cUpdateMonthAndYear

 

I think i understand all the other points fine.

[eos]

Can you elaborate on the:

• a script that finds all records for the last 12 months and sorts them by cUpdateMonthAndYear

 

You want to see a statistics for the past 12 months, so you need to find the records in that period; you also need to sort them by cUpdateMonthAndYear because that's the break field for your summary, and a sort is needed for the summary field to hold correct (i.e. grouped by Month&Year) results.

 

A script along these lines should work for you (when using your actual object names …) :

Enter Find Mode
Go to Layout [ Updates ( Updates) ]
Set Field [ Updates::updateDate ; ">" & Let ( cd = Get ( CurrentDate ) ; Date ( Month ( cd ) ; Day ( cd ) ; Year ( cd ) - 1 ) ) ]
Set Error Capture [ On ]
Perform Find
If [ not Get ( FoundCount ) ]
  # error handling steps, e.g. Show Custom Dialog, Go to Layout [ original ], Exit Script etc.
End If
Sort [ Restore ]
# sort by Updates::cUpdateMonthAndYear

Study the date functions and how you can over- and underfeed them (and let FileMaker figure our the actual date) to easily create the date range you want to search for; e.g. if you need to cover entire months, you could use a calculation like …

 

Let ( [
  cd = Get ( CurrentDate ) ;
  m = Month ( cd ) ;
  y = Year ( cd ) 
  ] ;
  Date ( m - 11 ; 1 ; y ) & ".." & Date ( m + 1 ; 0 ; y )
)

… which today returns (in US notation) 6/1/2013..5/31/2014 – but tomorrow would result in 7/1/2013..6/30/2014

[comment]

you also need to sort them by cUpdateMonthAndYear because that's the break field for your summary

 

That is correct - and that is also why you should not use:

 

Month ( YourTable::updateDate ) & "|" & Year ( YourTable::updateDate )

 

as the formula for the cUpdateMonthAndYear field. It will not sort correctly across the year boundary, and (being Text) it will also not sort correctly one-digit months and two-digit months among themselves. For example, the 12 months starting from June 2013 will be sorted as:

 

1|2014

10|2013

11|2013

12|2013

2|2014

3|2014

4|2014

5|2014

6|2013

7|2013

8|2013

9|2013

 

For this reason as well as others, it is preferable to calculate cMonth as =

UpdateDate - Day ( UpdateDate ) + 1

and set the result type of the calculation to Date.

[eos]

Right; I didn't test it and forgot about the year boundary (and the alpha-sorting). Thanks for the reminder.

[nesor]

Just came across this post (in June of 2015) and now I've got a solution to getting a chart to display a trend over years and months, sorted chronologically. While I had all of the components in place (calc field, sort, chart setup correctly), the calc field was defined incorrectly. I never would have figured the calc formula that @comment suggested. It worked. 

Thank you for the post and suggestions.