Grouping per half hour, per weekday

[Flupke]

In a salesprogram I wrote, I want to display the sales per half hour, per weekday (grouped by all mondays of the year, all tuesdays of the year, etc.

Every sale has its day and time in the database.

I think the grouping of a datetime allows grouping by the hour and not by the half hour. How can I achieve groupîng per half hour. How can I group per weekday?

Many thanks and greetings from Brugge (Bruges, Belgium),

Michel

 

[synapsevampire]

Try posting technical information, such as:

Crystal version
Database used
Example data
Expected output

You say that you want it grouped by weekday, is that really what you want because you'd get all Mondays in one group, all Tuesdays in another group, each rolled into one grouping, with the .5 hours in their respective groups for ONLY those 1/2 hours that have sales.

Here's a formula for 1/2 hours for existing data:

datetime(year({Table.DateTimeField}),
month({Table.DateTimeField}),
day({Table.DateTimeField}),
hour({Table.DateTimeField}),
(
if minute({Table.DateTimeField}) < 30 then
0
else
30
)
,0)

-k

 

[lbass]

You can use the following formula to group on the weekday:

DayOfWeek({table.date})

This will return a number from 1 to 7. If you want it to return a dayname then use:

Weekdayname(DayOfWeek({table.date}))

-LB

 

[Flupke]

That is exactly what I want. All mondays grouped together, all tuesdays, etc.
Within these groupes, all sales grouped per half hour.

I use an Access database with a table containing a datetime-filed.

When I use dayofweek({table.fieldname}) for grouping per weekday, I don't get all mondays, tuesdays, etc. together.

Can you help me with this, please?

Many thanks,

Michel

 

[lbass]

If you are using SV's formula, try wrapping it in time() as in:

time(
datetime(year({Table.DateTimeField}),
month({Table.DateTimeField}),
day({Table.DateTimeField}),
hour({Table.DateTimeField}),
(
if minute({Table.DateTimeField}) < 30 then
0
else
30
)
,0)
)

Now it will only return a time, not a datetime, and the dayofweek formula should now work correctly.

-LB

 

[lbass]

Actually I think you could just use the following for the time field to group on (assuming and outer group based on dayofweek):

time(hour({table.DateTime}),
(if minute({table.DateTime}) < 30 then
0
else
30),0)

-LB