Multiple Months on Sales Report
I am trying to generate a monthly sales reports for one of my clients. The sales report consists of 4 tables, with Table 1 being the 2009 sales details for this team of 9 sales people. With each Find for a specific sales person by name, I have created a calculation that goes to Table 2 to pull that sales person's Monthly Average Sales for 2007, then a separate calculation that goes to Table 3 to pull that sales person's Monthly Average Sales for 2008, then a separate calculation that goes to Table 4 to pull that sales person's Monthly Goals for 2009. On Layout 1 management is able to see full details of who the Found salesperson sold to, what they sold, what their profit was, and how they compare in sales to the monthly averages of 2007 & 2008, as well as how they are doing against their goal for 2009. Then on Layout 2, I consolidate all of this into a single sheet summary of information.
This works great when only looking at one month at a time, however as the year progresses I need to be able to accommodate viewing multiple months of data simultaneously. The challenge to this becomes how to determine how many months the query is based upon, (are they looking at January February March = 3 months, or January - June = 6 months, etc.) and then to calculate that number of months times the monthly averages of 2007, 2008, and the goal for 2009.
The report that I am getting this data from doesn't output a date for the report, however it is easy enough for me to input a date for each months worth of data. When importing the data into Table 1, I can simply Replace Field Contents and put a month's name in, ie. for January I put in January, for February I will put in February, etc. I don't have any ability to identify sales in Table 2 or 3 (2007 or 2008 data) because that data was generated at the end of each year, so I can only take the results and divide by 12 to get a monthly average to compare against. I am assuming I will need to accommodate a Begin Date (i.e. January) and an End Date (i.e. March), but how can I identify that I need to multiply all other data times 3 in order to track that salesperson's success against their sales?
Any help would be greatly appreciated.