Sub totals for the month: First, Second, Third and Weekend

[rkckjk]

Thanks a lot lbass, the formula works great, I was trying to create sub totals for each of the four groups(First, Second, Third and Weekend) to be placed at the end of the report. The current formula does total the amount for each group per day - I'd like to have the total for each group for the whole file. I figured out on how to get the grand total = (First + Second + Third + Weekend), but I need the sub totals.

if (dayofweek({table.datetime}) = 7 and
time({table.datetime}) >= time(8,0,0)) or
dayofweek({table.datetime}) = 1 or
(dayofweek({table.datetime}) = 2 and
time({table.datetime}) < time(7,0,0)) then "Weekend" else

if dayofweek({table.datetime}) in 3 to 7 then
(if time({table.datetime}) in time(0,0,0) to time(7,59,59) then "Third Shift" else

if time({table.datetime}) in time(8,0,0) to time(15,59,59) then
"First Shift" else

if time({table.datetime}) in time(16,0,0) to time(23,59,59) then "Second Shift") else

if dayofweek({table.datetime}) = 2 and time({table.datetime}) in time(7,0,0) to time(15,59,59) then
"First Shift" else
if time({table.datetime}) in time(16,0,0) to time(23,59,59) then "Second Shift" else "Other"

 

[lbass]

I don't know what your report looks like, but you could probably insert a count on any recurring record to get the grand total (if your summary is a count). To get the breakout by shift, you could use a crosstab, using the shift formula as the row field, and adding a summary field (no column field).

If for whatever reason the crosstab doesn't work for your needs, you could use running totals where you use "evaluate based on a formula" and use formulas like the following as:

{@shift} = "First Shift"

Reset never.

-LB

 

[rkckjk]

Thanks again I used the evaluate based on formula routine and it work great.