AnsweredAssumed Answered

Creating a yearly trend chart based on monthly record count

Question asked by InvectusIlumni on Feb 20, 2011
Latest reply on Feb 21, 2011 by philmodjunk


Creating a yearly trend chart based on monthly record count


Hey guys,

I 'd like to create a line chart that shows a trend of bookings (jobs) made by company XYZ on a mnthly basis.

The chart will be featured on the home layout that is reading from the CUSTOMERS table. 

I have a JOBS table that keeps track of all booking details and it is related to the CUSTOMERS table by a 1-to-many relationship where the CUSTOMERS table is the parent.

I currently have the chart setup like this: 

Type: Line

X Axis => If ( PREFERENCES::year_current=JOBS::year_service;JOBS::month_service)

Y Axis => JOBS::resolution_success_count

The JOBS::resolution_success_count field is a summy field that is outputting the total of JOBS::resolution_success, which is a calculation field that counts the number of successful bookings and has the following calculation : 

Exact (Resolution;"Success")

What I what to do is to restrict the chart to the current year only. In other words, it should only be based on bookings whose service year is equal to the current year. This is why my X Axis is defined by this calculation:

If ( PREFERENCES::year_current=JOBS::year_service;JOBS::month_service)

 Last, but not least, the chart is using data from current found set so it's not limited to the current record.

Any help will be appreciated