# Crystal Reports - Calculating business days to forecast sales trend

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 :-)
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Look at this formula that calculates work days.

mlmcc
Commented:
rsg71, it is pain to compare working days to your CAL_BUS_DT_XX... especially the number of working days for each month is not the same. It would be nice if you could have a total working days field.

CAL_ACCTNG_PER     Total_Work_Day    CAL_BUS_DT_1     CAL_BUS_DT_2     CAL_BUS_DT_3
200601                     22                        03-Jan-06             04-Jan-06             05-Jan-06
200602                     20                        01-Feb-06             02-Feb-06            03-Feb-06
200603                     23                        01-Mar-06             02-Mar-06            03-Mar-06

What do you think?

dylan
Author Commented:
mlmcc,
I have seen those ideas, but they involve deducting holidays.  I have the opposite data, I have only the working days.

dylan,
it is easy to modify the sheet / table CAL_BUS_DT to have a field count the working days.  This would give me a total working days for a given period.  But even with total working days, how do we count how many working days have passed?

Commented:
Yeah thats right, even with total working day, it is almost impossible to count how many working days have passed.
But I still prefer to have a fix column in your excel/table which contain all working days. I have a another suggestion here, change your excel columns to:

WYear    WMonth    WDay
2006       01            03
2006       01            04
.
.
.
2006       02            01
2006       02            02
.
.
2006       03            01
.
.

Since the table contain all the working days with fixed column, I think it would be easier for you to do your calculation. You could compare today's date with WMonth, WYear & WDay field to find out how many days is passed.

dylan
Author Commented:
dylan,
You wrote "You could compare today's date with WMonth, WYear & WDay field to find out how many days is passed."  Can you kindly give an example?

Thanks,
Roy
Commented:
Try this select statement (Pls convert it if you are using excel)

SELECT    Count(*) DayPassed
WHERE     Year(getdate()) = WYear
AND Month(getdate()) = WMonth
AND Day(getdate()) <= WDay

dylan
Commented:
If you are using sql server table, try below

SELECT    SUM(CASE
WHEN Day(getdate()) <= WDay THEN 1
ELSE 0
END) AS DayPassed,
Count(*) TotalDays
WHERE     Year(getdate()) = WYear
AND Month(getdate()) = WMonth

This will give you the total working day passed & total working days in that month. You can then make use of this 2 information for your other calculation.

dylan
Author Commented:
dylan,
WDay - Is this the day of the month (3,5,9,14,23rd, etc) or the total # of working days defined for a period?

I am not using sql server, rather just incorporating an external table or sheet into an existing Crystal report.
Author Commented:
dylan,
I see, you want a complete detailed record for each year/month/day.  I'll work on some data and let you know...
Author Commented:
dylan,
excuse my ignorance on this; I created a table called calendar, with data as follows:
Wyear      Wmonth      Wday      Date
2006      1      1      03-Jan-06
2006      1      2      04-Jan-06
2006      1      3      05-Jan-06
2006      1      4      06-Jan-06
2006      1      5      09-Jan-06
2006      1      6      10-Jan-06
2006      1      7      11-Jan-06
2006      1      8      12-Jan-06
2006      1      9      13-Jan-06
2006      1      10      16-Jan-06
.......etc for the whole year

SELECT    SUM(CASE
WHEN Day(getdate()) <= WDay THEN 1
ELSE 0
END) AS DayPassed,
Count(*) TotalDays
WHERE     Year(getdate()) = WYear
AND Month(getdate()) = WMonth
FROM Calendar;
Commented:
No rsg71, the table should only contain the Working Day:

Wyear     Wmonth     Wday     Date
2006       1               3            03-Jan-06
2006       1               4            04-Jan-06
2006       1               5            05-Jan-06
2006       1               6            06-Jan-06
2006       1               9            09-Jan-06
2006       1               10          10-Jan-06
2006       1               11          11-Jan-06
2006       1               12          12-Jan-06
2006       1               13          13-Jan-06
2006       1               16          16-Jan-06

And the sql for Access should looks like:

SELECT    SUM(IIF(Day(getdate()) <= WDay, 1, 0)) AS DayPassed, Count(*) TotalDays
WHERE     Year(getdate()) = WYear
AND Month(getdate()) = WMonth
FROM Calendar;

dylan
Author Commented:
dylan,
No workie.
Shouldn't there be an AS before TotalDays? Either way, Access is stuck on WHERE.
Commented:
Sorry rsg71, the sql statement in access should be

SELECT    SUM(IIF(datepart('d', date()) <= WDay, 1, 0)) AS DayPassed, Count(*) AS TotalDays
FROM Calendar
WHERE     datepart('yyyy', date()) = WYear
AND datepart('m', date()) = WMonth;

You won't get any result for Jan because today is 24 of March :) Unless you try out with March data.

dylan
Author Commented:
dylan,
thanks for the proper usage.  It ran. I do have March data.  The results are incorrect:
DayPassed      TotalDays
7      23

Total Days are correct. DayPassed should equal 16 if not including today's date (March 23rd).
Commented:
Sorry again, to find out the day passed, it should be

SUM(IIF(datepart('d', date()) >= WDay, 1, 0)) AS DayPassed

dylan
Author Commented:
That results in 17 days, which I verified the data and the correct days should be 16.  I threw a -1 into the statement and it works...
SELECT SUM(IIF(datepart('d', date()) >= WDay, 1, 0))-1

DayPassed      TotalDays
16      23

Now, let me try linking this sheet / table to Crystal, and see if I can get it to work.  Don't hold back any ideas, as you are on the right track...tks
Commented:
Don't use -1, use this instead:
SELECT SUM(IIF(datepart('d', date()) > WDay, 1, 0))

I think with this infomation you should be able to achieve the task ;) it's 1am in my country and its time for me to get sleep :)
Do post your question here if you need further help, I will reply by tmr and some other experts will help as well.
good luck~

dylan
Author Commented:
get to sleep. It's 12pm here, I will post results as work progresses today. Thanks for your efforts.
Author Commented:
Crystal Experts...
If I put this field / formula into my report, the report just keeps going for thousands of pages (I am placing in the report footer.  Second, if we do get it in there successfully, how do I get it to total being it is only placed into a summary area (there will not be a '1' for each detail record).

If (datepart('d', Currentdate()) > {Sheet1_.Wday}) then
If (datepart('yyyy',Currentdate())={Sheet1_.Wyear}) And datepart('m',Currentdate())={Sheet1_.Wmonth}
then 1 else 0
Author Commented:
Okay, instead of linking directly to Crystal, I added a new Access DB called Calendar with a table containing the spreadsheet, a query as Dylan as provided above.  Now crystal pulls directly from the Access query.  Any other ideas?  Otherwise Dylan's solution works in this manner, not much work on Crystal, though.
Commented:
rsg71, I think my solution is the best for you, since doing processing in Database layer will be much more efficiency than doing it in CR.

There are some improvement which you could do,
1) instead of hardcode the today's date into the query, in CR's Command, you can create a parameters {?theDate} and pass in the date that you want to investigate on, each time you run the report it will then prompt you to key in a date:

SELECT    SUM(IIF(datepart('d', {?theDate}) > WDay, 1, 0)) AS DayPassed, Count(*) AS TotalDays
FROM Calendar
WHERE     datepart('yyyy', {?theDate}) = WYear
AND datepart('m', {?theDate}) = WMonth;

2) Instead of create 3 fields WYear, WMonth, WDay, you could actually just create 1 date field: WDate that contain all the working days:

WDate
03-Jan-06
04-Jan-06
05-Jan-06
06-Jan-06
09-Jan-06
10-Jan-06
11-Jan-06
12-Jan-06
13-Jan-06
16-Jan-06

SELECT    SUM(IIF(datepart('d', date()) > datepart('d', WDate), 1, 0)) AS DayPassed, Count(*) AS TotalDays
FROM Calendar
WHERE     datepart('yyyy', date()) = datepart('yyyy', WDate)
AND datepart('m', date()) = datepart('m', WDate);

dylan

Experts Exchange Solution brought to you by