Translate Date/Time to Day 1

[JerryJay]

I have a report which uses a date/time field. The data is stored in the database as follows (9/17/2005 8:00:00). I used the filters in Crystal to display the information Sat.

My next goal is to add the common days of the week in the column and then divide them by the total number of occurences. I am trying to come up with a percentage of occurences on the days of the week.

Could someone help me with the formula so that it recognizes the day of the week.

 

[MisterMo]

you could use something like
@DayOW

DayOfWeek ({Table.DateTimeField})

and then and 6 0r 7 running totals which would count only certain days on the Evaluate Section click on the X-2 button
and add

@DayOW = 1,2,3,4.... one each day

remember that the day of the week in Crystal start from Sunday 1 to Saturday 7



-Mo

 

[JerryJay]

mister mo,

Thanks for the information. I understand what you mean by DayofWeek. Could you forward the formula to add these items

 

[MisterMo]

add your formula to the detail section, this can be invisible.

Right click on the formula and choose Insert > Running Totals give it a name

eg TotMonday

choose count

evaluate section click the X-2 button

add @DayOW = 2

repeat the process six or seven times if you need Sundays.


-Mo

 

[synapsevampire]

You might use a cross-tab, and create a formula for the date as Mistermo suggested:

DayOfWeek ({Table.DateTimeField})

Make this the Column, and then place a field to count in the summary section.

You can then request that it display the percentages by selecting the show as a percentage of the column in the options area.

This is Crystal version dependent, please remember to supply basic information in your posts.

Anyway, this leverages Crystal's built in functionality, so it's easy to set up.

-k

 

[JerryJay]

Mo and Synpsevampire,

Thanks for the suggestions. I am using Crystal 8.5. I keep going around in Circles. I have gotten the percentage to show but in a cloulunar format. Example:

Date Desc of error Day of week of Error
Pecentage

11-05-05 Med missed Thursday
6.67%

I am attempting to place the percentage in a format as such

Day: Sun 10% Monday 10% Tue 10% Wed 10% Thu 10% Fri 25% Sat 25%

I eventually will be taking percentages from several other fields.

Any other suggestions will be greatly appreciated.

 

[synapsevampire]

Defining requirements is generally the problem here.

Yo say you have other percentages as well, but you don't state where they will be displayed.

The cross-tab resolves, except that you didn't state output, so you'll need to switch to a manual cross-tab and roll your own percentages.

So the 3 formula method such as:

Details
whileprintingrecords;
numbervar sun;
numbervar mon;
numbervar tue;
numbervar wed;
<etc>
If dayofweek({table.date}) = 1 then
sun:=sun+1
else
If dayofweek({table.date}) = 2 then
mon:=mon+1
If dayofweek({table.date}) = 3 then
tue:=tue+1
<etc>

Then in the report footer you can use formulas for each day, here's Sunday:
whileprintingrecords;
numbervar sun;
numbervar mon;
numbervar tue;
numbervar wed;
<etc>
(sun/(sun+mon+tue+wed...<etc>)/100&"%"

Should allow you to work it all out.

-k