For something like this, I typically set up a separate table that holds the values, which are updated via script whenever you close the books for that month. This way, the figures are indexable and the reporting (including charts) will perform very fast, as opposed to waiting around on summary fields (or ExecuteSQL results).
I've built one of these this week. I use a scheduled script on the server to calculate:
Sales this month
Sales this year to date
Sales last year to the same date
Growth (Sales this year to date - Sales last year to same date)
Sales for the whole of last year
Timestamp when data was last calculated
It uses a preference value for the financial year end then a looping script to set the query dates and get each of the values as variables.
Finally it sets three variables containing, separated by carriage return, month name, Last year's sales for month and this year's sales for month. It then goes to the table underlying our home layout and puts the variables into fields.
When the user logs in, they see a dashboard view that shows the sales figures and a graph showing the delimited sales data to compare months. It's a graph with month name on the x-axis and two y-axis series showing last year's and this year's figures. We have a load of other information on the dashboard showing quotes pipeline by month, stock needing reordering etc, all calculated by the scheduled script.
The scheduled scrip runs every hour but if a user needs to update it between these I have a refresh button that lets them run the script on the server.
I hope this helps....
This is sort of unique in that it is unit sales and not accounting based. So we never really close the month...This report is based off of retail registrations. So it is July, but I might get one today that was sold in May. When we enter this into the system, the sold date gets put in (not the registration date). So it needs to be live.So if I run the report tomorrow, the May numbers will pick up the latest registration that we get.
Write a script that updates the relevant fields in the totals table whenever anything happens that affects the total. Use Script Triggers or embed the actions into the script that performs the update.
I can sort of get this to work.
Right now if I create a find for units sold in 2015 and 2016, I get the record set that I need. I sort for Month sold, then year sold and have a sub-summary that counts how many records are in there. That gives me a layout that shows Jan 2015 totals, then Jan 2016 totals. When I chart that, it just gives me totals per month. (combing 2015 and 2016)
Is there a way to specify in the chart calculation on the Y series data, to pull only 2016?
You need two data series. You can either do that by doing what we're telling you to do - store the records by month in a table - or by creating two sets of return delimited values in global variables and use that.
Sorry, I haven't had to work on this database (other than trivial stuff) for about 2 years...so my somewhat limited skills are worse yet. I'm going to do a little re-educating and I'll check back when I am able to test these options out.