[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

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 :-)
0
rsg71
Asked:
rsg71
  • 12
  • 10
1 Solution
 
mlmccCommented:
Look at this formula that calculates work days.

http://www.kenhamady.com/form01.html

mlmcc
0
 
dylanyeeCommented:
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
 
rsg71Author 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?


0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
dylanyeeCommented:
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
 
rsg71Author 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
0
 
dylanyeeCommented:
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
 
dylanyeeCommented:
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
 
rsg71Author 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.
0
 
rsg71Author 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...
0
 
rsg71Author 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

Using Access sql query, please advise if I am missing something

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
 
dylanyeeCommented:
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
 
rsg71Author Commented:
dylan,
No workie.  
Shouldn't there be an AS before TotalDays? Either way, Access is stuck on WHERE.
0
 
dylanyeeCommented:
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
 
rsg71Author 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).
0
 
dylanyeeCommented:
Sorry again, to find out the day passed, it should be

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

dylan
0
 
rsg71Author 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
0
 
dylanyeeCommented:
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
 
rsg71Author Commented:
get to sleep. It's 12pm here, I will post results as work progresses today. Thanks for your efforts.
0
 
rsg71Author 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
0
 
rsg71Author 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.
0
 
dylanyeeCommented:
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

and change your query to:

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
 
rsg71Author Commented:
Thanks Dylan for all your efforts, I put them to use and came out with the results I needed.
0
 
dylanyeeCommented:
glad I could help~

dylan
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now