Solved

# Crystal Reports - Calculating business days to forecast sales trend

Posted on 2006-03-22
995 Views
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 :-)
0
Question by:rsg71

LVL 100

Expert Comment

Look at this formula that calculates work days.

mlmcc
0

LVL 8

Expert Comment

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
0

Author Comment

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?

0

LVL 8

Expert Comment

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
0

Author Comment

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
0

LVL 8

Expert Comment

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
0

LVL 8

Expert Comment

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
0

Author Comment

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.
0

Author Comment

dylan,
I see, you want a complete detailed record for each year/month/day.  I'll work on some data and let you know...
0

Author Comment

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;
0

LVL 8

Expert Comment

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
0

Author Comment

dylan,
No workie.
Shouldn't there be an AS before TotalDays? Either way, Access is stuck on WHERE.
0

LVL 8

Expert Comment

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
0

Author Comment

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).
0

LVL 8

Expert Comment

Sorry again, to find out the day passed, it should be

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

dylan
0

Author Comment

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
0

LVL 8

Expert Comment

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
0

Author Comment

get to sleep. It's 12pm here, I will post results as work progresses today. Thanks for your efforts.
0

Author Comment

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
0

Author Comment

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.
0

LVL 8

Accepted Solution

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
0

Author Comment

Thanks Dylan for all your efforts, I put them to use and came out with the results I needed.
0

LVL 8

Expert Comment

dylan
0

## Featured Post

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods youÂ´d like to investigate in more detail.  The methods are covered in more detail in oâ€¦