Formula to count certain weekdays 2

[sorchard2000]

I am using Crystal 8.5 with SQL DB.
I am trying to create a formula that will count each weekday in a daterange that the user puts in. Example:
User enters date range of:
09/6/04 to 09/17/04

Formula needs to display:

Day of week # of times in range
Monday 2
Tuesday 2
Wednesday 2
Thursday 2
Friday 2
Saturday 1
sunday 1

Thanks in advance for help!

Sorchard2000

 

[synapsevampire]

Here's Ken's formula:

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

Substitute your minimum({?dateparm}) and
maximum({?dateparm}) for the start and end dates.

-k

 

[fredp1]

To count the weekdays in a rabge , this is what I would do,

Create a formulae to get the weekday number
{@weekdaynumber}
weekday({fieldname})

Now create a group on above formulae (Insert group)...
but customise the Group name field to display the name of the week by using a formulae (click on the x+2)
weekdayname({@weekdaynumber})

Now you can add a count summary of the {@weekdaynumber} variable.




Cheers
Fred

 

[synapsevampire]

Sorry, I should have read the requirements more closely, Fred looks spot on except if you want only weekdays, suppress other values

whileprintingrecords;
numbervar Counter;
numbervar array DayCount;
redim preserve daycount [5]
For counter := 1 to datediff("d",minimum({?dateparm}),maximum({?dateparm})) do(
if weekday({table.field}) in 2 to 6 then
DayCount[(weekday({table.field})-1]:=DayCount[(weekday({table.field})-1]+1
)

Now you can display each using:

whileprintingrecords;
numbervar array DayCount[1] // monday

Or for Tuesday:

whileprintingrecords;
numbervar array DayCount[2] // monday

Looks right...

-k

 

[MrBillSC]

Here is another method:

Create a formula @NumOfDays that calcs the number of each day of the week between the start and end dates:

WhilePrintingRecords;

NumberVar NumOfDays:= {?EndDate} - {?StartDate} + 1;
Numbervar SunCnt;
Numbervar MonCnt;
Numbervar TueCnt;
Numbervar WedCnt;
Numbervar ThuCnt;
Numbervar FriCnt;
Numbervar SatCnt;
Numbervar x;

// Loop for number of days in range.
For x:=1 to NumOfDays do (

// Increase count for correct day of week.
If DayOfWeek({?StartDate} + x - 1) = 1 then
SunCnt:= SunCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 2 then
MonCnt:= MonCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 3 then
TueCnt:= TueCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 4 then
WedCnt:= WedCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 5 then
ThuCnt:= ThuCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 6 then
FriCnt:= FriCnt + 1 else
If DayOfWeek({?StartDate} + x - 1) = 7 then
SatCnt:= SatCnt + 1 );

Place the above formula in your report header section and suppress if you like.

Next Create a formula to display each of the Cnt fields calculated in the formula above:

@MonCnt formula:

WhilePrintingRecords;

Numbervar MonCnt;
MonCnt

You can now display these across or down, they are not dependent on grouping or values in your data.

MrBill

 

[synapsevampire]

Oooops:

whileprintingrecords;
datevar MyDate:=minimum({?mydate});
numbervar Counter;
numbervar array DayCount;
redim preserve daycount [5];
For Counter := 1 to datediff("d",minimum({?mydate}),maximum({?mydate})) do(
if weekday(MyDate) in 2 to 6 then
DayCount[weekday(MyDate)-1]:=DayCount[weekday(MyDate)-1]+1;
MyDate := MyDate+1;
);

I royally screwed that up, sorry...

The output should be fine though...

-k

 

[fredp1]

MrBill,

That a very nice technique I've never used before.
That deserves a star!




Cheers
Fred

 

[synapsevampire]

Fred, try using the following, same concept, just a bit tidier (don't look at my first post...pure insanity at play):

Report header formula
whileprintingrecords;
datevar MyDate:=minimum({?mydate});
numbervar Counter;
numbervar array DayCount;
redim preserve daycount [5];
For Counter := 1 to datediff("d",minimum({?mydate}),maximum({?mydate})) do(
if weekday(MyDate) in 2 to 6 then
DayCount[weekday(MyDate)-1]:=DayCount[weekday(MyDate)-1]+1;
MyDate := MyDate+1;
);

To display, use:
whileprintingrecords;
numbervar array DayCount;
"Monday total = " +totext(DayCount[1])

Changing the number from 1 to 5 for each day of the week.

Fast and simple...

-k

 

[fredp1]

Although this isn't my post, I like the use of an array.
I must admit I normally avoid them because I don't understand them that well, but your example makes the code nice and compact.
I also like the use of a For do loop... again, something that I have't used.

This will give Sorchard2000 3 ways of approaching the problem.

A star to you too synapsevampire!

Cheers
Fred

 

[sorchard2000]

Cheers to all the people who helped me: you all are far smarter than me! So, I get to continue with my stupid questions.

I used MrBillSC's formula because I needed all the days of the week listed and that worked beautifully. Values were returned for every formula.

But when I use that formula (for example, "MonCnt") in another formula to figure percentile, I get a "Division by Zero" error.
formula:
((((({@MonCnt}*4800) - Sum ({@mondayminusedshift1}))/({@MonCnt}*4800))*100)-100)*-1

The value for @MonCnt = 2.
The value for @mondayminusedshift1 = 5423

So there are no zeros in my data.
I'm a beginner at writing formulas so I'm sure I've done something wrong.

When I take the ({@MonCnt}*4800) out of the formula and put in a plain number value, the formula works great.

HELP! It's driving me crazy!

Thanks in advance.
sorchard2000

 

[kskid]

Where is the formula located in the report? It should be in the group footer. If it's in the group header, then @MonCnt would be reset to 0.

 

[fredp1]

Do you have a WhilePrintingRecords; statement as the first line of your formulae? (...you should)
I think that should work.
You'll notice that the @MONCnt formulae has the whileprintingrecords statement in it.
This tells Crystall to evaluate the formulae when its printing the data (Pass #2). I think because that you have a Sum() function in your formulae it evaluates the formulae sooner (pass #1 or pre pass #2 I think) than you want it i.e. when @Moncnt is 0.
I hope that works.
Have a look at the help under "Multi-pass reporting flow chart" to get a better picture/explanation of the various Passes in Crystal.

Cheers
Fred

 

[synapsevampire]

Try:

Report header formula
whileprintingrecords;
datevar MyDate:=minimum({?mydate});
numbervar Counter;
numbervar array DayCount;
redim preserve daycount [7];
For Counter := 1 to datediff("d",minimum({?mydate}),maximum({?mydate})) do(
DayCount[weekday(MyDate)]:=DayCount[weekday(MyDate)]+1;
MyDate := MyDate+1;
);

To display, use:
whileprintingrecords;
numbervar array DayCount;
"Sunday total = " +totext(DayCount[1])

To display, use:
whileprintingrecords;
numbervar array DayCount;
"Monday total = " +totext(DayCount[2])

etc...

-k

 

[sorchard2000]

Thanks both WichitaKid and fredp1! Those 2 suggestions helped it to work properly!
Thanks!
sorchard2000