Creating a yearly trend chart based on monthly record count
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:
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 :
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