Calculating next business day 2

[paljnad]

Hello,

I have a Crystal Report v 10.0 that has dates and the balances for that day in the detail section and it is grouped by account number and date so it looks like this:

Acct Num: 1234
9/1 1000.00
9/2 134.56
9/3 877.34
9/4 3444.44
9/8 635.78

Acct Num: 3456
9/1 1000.00
9/2 134.56
9/3 877.34
9/4 3444.44
9/8 635.78

The parameters for the report is 'StartDate' and 'EndDate' so the dates on the report are between 'StartDate' and 'EndDate'.

I want to calculate an average for the specified time period and also include weekends and holidays. For example, the average balance over the period 9/1 to 9/8 should include balances from 9/1 through 9/4, the balance from 9/4 for the weekend dates 9/5 and 9/6 and the holiday date 9/7, and the balance from 9/8.

I thought I could do this by putting a hidden field in the detail section to calculate the number of days until the next business day for each date on the report. Then I could multiply a day's balance by the number of days in this hidden field and use the results to arrive at a weighted average.

But I dont know how to calculate the next business day. I looked at some earlier posts and I can calculate the number of business days in a given date range etc. but not the next business day.

Please help.

 

[Madawc]

To allow for weekends starting from a weekday, try something like

if DatePart ("w", {date1}) = 6 
then CUrrentdate + 3
else currentdate + 1

But I think you're taking the wrong approach. If you are able to get tables / datasets added to the database? If you got a table of date set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that LEFT OUTER does not work if you also do a selection on the table receiving the LEFT OUTER link.)

If you can't add a table, you could try creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crsytal 11, you can also duplicate formula fields using the Field Explorer.


Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[paljnad]

Thanks, Madawc.

But I just want to be able to get the next business day.
If today was 9/1 then the next businessday is 9/2 and if today was 9/4 then the next businessday is 9/8 since 9/7 is a holiday.

 

[lbass]

See

http://www.kenhamady.com/formula.html,

formulas 1 and 17. You should be able to use one of these to accomplish the task.

-LB

 

[paljnad]

Thanks, lbass. I was able to use formula 17 to get what I wanted.