Date count for a specific day of week 1

[dannab]

I have a report that pulls down all data available when the timestamp dayofweek = 2 (Monday).

Pulls data from 12/29/03 - 03/08/2004.

The data pulled is then grouped by time interval and summarized.

In order to create averages, I must divide certain fields by the the number of Mondays in the timestamp range.



dannab

 

[dannab]

What I really need is a formula that will count the number of Mondays in the timestamp range.

dannab

 

[bdreed35]

Have you tried using the DistinctCount summary function?

If you create a distinct count over your date field than you should get the number of Mondays that are returned in your range.

~Brian

 

[dannab]

I had to create two formulas.

1. dateformat formulat to turn my timestamp into mm/dd/ccyy format (the field also includes mmhhss which I didnt want to use).
Local NumberVar myyear := Year ({iApplicationStat.Timestamp});
Local NumberVar mymonth := Month ({iApplicationStat.Timestamp});
Local NumberVar myday := Day ({iApplicationStat.Timestamp});
Local StringVar mydate := Totext (mymonth) + Totext (myday) + Totext (myyear) ;
2. to get the count of mondays
DistinctCount ({@dateformat})

Thanks for your help.

dannab

 

[poujor]

Hi

Use:
DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)
interval type is "ww" and firstdayofweek is crmonday

 

[kenhamady]

Poujor,

I had never needed the ww argument in DateDiff, but today I found a use for it and remembered this post. When I used it I found that if the StartDate is on a Monday and the EndDate is also on a Monday, it will only count one of them. If you are trying to count the number of Monday's in a period and you want to include both the Start and End Dates, you should Subtract one from the StartDate in the formula:

DateDiff ('ww',{?Date from} - 1 ,{?Date to} , crMonday)

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com