Counting number of Mondays

[krpurcel]

I am pulling several months worth of data. The data contains multiple rows for each date. One of my objectives is to get a count of the number of Mondays, number of Tuesdays, number of Wednesdays etc. contained in my data.

Since there are multiple rows of data for each date, I tried grouping by date. My thought was if I grouped by date, I could then use dayofweek and weekdayname to determine the count of the different days. However I keep getting an error when I try to create a formula using dayofweek at the group level.

At the detail level both of these formulas work fine:
1. dayofweek({CallArrivalPattern.CallDate})
2. weekdayname(dayofweek({CallArrivalPattern.CallDate}))

However this does not work:
DayOfWeek (GroupName ({CallArrivalPattern.CallDate}, "daily"))

It tells me "a date is required here" and highlights this:
GroupName ({CallArrivalPattern.CallDate}, "daily")

Since what I have rolled up at the group level are dates, I thought the group level data would be recognized as a date. Perhaps I have the syntax wrong?

I would like to understand what I am doing wrong here and how to use dayofweek at the group level. There may also be a much simpler way to try and achieve my objective of counting the days. I'd appreciate any advice you can give me.

Thanks!

 

[MCuthill]

krpurcel:
A brute-force solution is one formula field for each day of the week and place the following formula.

@COUNTER_Monday
IF WeekDayName(DayOfWeek({CallArrivalPattern.CallDate})) = "Monday" THEN 1 ELSE 0
Then SUM this formula to the group level.

This could (most likely) also be done using one formula field and incrementing a variable for each day of the week based on an evaluation of DayOfWeek.
@COUNTER_DayOfWeek:
WhilePrintingRecords;
Shared NumberVar Monday;
Shared NumberVar Tuesday;
... etc
... etc

IF WeekDayName(DayOfWeek({CallArrivalPattern.CallDate})) = "Monday" THEN Monday:=Monday+1 ELSE
IF WeekDayName(DayOfWeek({CallArrivalPattern.CallDate})) = "Tuesday" THEN Tuesday:=Tuesday+1 ELSE
... etc
... etc

You then need one Field reseting all variables in the Report header and one field for each day in the report footer to display the results.

@VariableReset_AllDays:
WhilePrintingRecords;
Shared NumberVar Monday:=0;
Shared NumberVar Tuesday:=0;
etc

@VariableDisplay_Monday:
WhilePrintingRecords;
Shared NumberVar Monday;

Hope this helps!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[aks12]

I hope I'm not misunderstanding your intentions, but if you just need a count of Mondays, Tuesdays, Wednesdays, etc. why not create a group on your weekdayname(dayofweek({CallArrivalPattern.CallDate})) field and then create a summary count on your group?

 

[MCuthill]

@aks12:
Wow... can't believe I overlooked that option.

Since there are multiple rows of data for each date, I tried grouping by date. My thought was if I grouped by date, I could then use dayofweek and weekdayname to determine the count of the different days. However I keep getting an error when I try to create a formula using dayofweek at the group level.

But perhaps a DISTINCTCOUNT of the {CallArrivalPattern.CallDate} when grouped on WeekDayName?
Or if it is a Date/Time field (and the time creating several unique records for same date), a DISTINCTCOUNT of Date({CallArrivalPattern.CallDate}).

My Apologies for the other posting when something like this may work.

Cheers!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[lbass]

Your formulas would work if you omitted "groupname" and just used the field itself.

DayOfWeek({CallArrivalPattern.CallDate})

I have NEVER had occasion to use groupname in a formula.

Ken Hamady has a method for counting days of the week if you are trying to count the number of weekdays in a certain date range.

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

See formula#24.

-LB

 

[krpurcel]

First - thanks very much for the replies! Let me try and clarify.

I have limited my sample to two days for development purposes, 04/03/09 and 04/10/09 (two Fridays). There are multiple entries for each date. The detail section shows 44 rows of data for the date 04/03/09 and 41 rows of data for the date 04/10/09. So I am grouping on the [{CallArrivalPattern.CallDate}] field to roll this up.

At that group level I have the following formulas.
1. [dayofweek({CallArrivalPattern.CallDate})] - This shows me the corresponding number for the day of the week.
2. [weekdayname(dayofweek({CallArrivalPattern.CallDate}))] - This gives me the day of the week name.
Both of these work fine.

The problem arises when I try to roll this up to a count of individual days of the week. I tried doing a summary of [weekdayname(dayofweek({CallArrivalPattern.CallDate}))] in the GrandTotal (report footer). If I use COUNT it returns 85 (the total number of Fridays listed in the detail section). If I use DISTINCTCOUNT I get 1 (since all the data being used has a Friday date). What I need to get is "2", the number of Fridays in my sample.

I decided that since the formula [weekdayname(dayofweek({CallArrivalPattern.CallDate})] worked at the detail level I would create another formula doing the same thing at the group level.

I am using Crystal XI R2. In the formula editor I selected "DayOfWeek". Once it was in place and the cursor was inside the (), I double clicked the group name under report fields. What it gave me was [DayOfWeek (GroupName ({CallArrivalPattern.CallDate}, "daily"))]. That's why GroupName was in the formula LBass. And even without GroupName in the formula, the formula fails. It does not like the "daily" at the end of [DayOfWeek ({CallArrivalPattern.CallDate}, "daily")]. It highlights it and tells me a number is required here. LBass, in your response you say to omit groupname and just use the field name, but your example is the formula I am using at the detail level. It doesn't include the group identifier "daily" on the end. If I remove "daily", then I am back to the same formula I use at the group level and get 85 or 1 depending on whether I use count or distinctcount.

the problem as I see it is - in order to stop getting counts of the records in the detail section, I need to summarize at the group level. However when I try and create a summary based on the group, the formula editor tells me "a number is required here" when it evaluates the string [DayOfWeek ({CallArrivalPattern.CallDate}, "daily"].

Thanks guys. I appreciate all the time and effort you have expended trying to help me.

 

[krpurcel]

Guys,

Ken Hamady's formula that LBass referred me to works like a charm. And so much simpler.

Thanks for all the help!