I have been using a sales summary report for quite a long time (Crystal 10) without any trouble. I am trying to use sales data within the current month to calculate the trend, and to forecast the sales at the end of the month. Since we are talking business days here, it is only Monday thru Friday which has sales, less holidays. Therefore, just dividing by the number of days in the current month wont' work.
I have a table which has the sales data ({cur_sales}). I also have a spreadsheet which contains a calendar of all the business days for this year. The calendar looks something like this:
CAL_ACCTNG_PER CAL_BUS_DT_1 CAL_BUS_DT_2 CAL_BUS_DT_3
200601 03-Jan-06 04-Jan-06 05-Jan-06
200602 01-Feb-06 02-Feb-06 03-Feb-06
200603 01-Mar-06 02-Mar-06 03-Mar-06
So if today is 22-Mar-06, and {cur_sales}=70000, and given the table (or spreadsheet) CAL for period 200603 contains 23 business days, there are 7 business days as of today left for the month. The current sales trend is (23-7)=16 business days was used to create those 70000 in sales, (70000/16)=4375 per day. With 7 business days left in the month, the sales forecast for the month is (23*4375) $100,625.
Now, how do we put this into a formula?
So far I know we need to compare today's date (month and year) to the field CAL_ACCTNG_PER to ensure we are in the right period, then compare today's date with the business date in the calendar (CAL_BUS_DT_XX). After that I am at a loss! Do we count records?
tks :-)
http://www.kenhamady.com/form01.html
mlmcc