Rollover running totals to next day column

[Dross]

I have a report using CR 2011 and it has a column for each day (Monday - Sunday) and does employee counts by the hours of clock ins and clock outs. I have a reset variable for each day but I need to figure out how to rollover counts from Monday to Tuesday. For example on Monday at 10PM I have 1 employee clock in and then at 11PM I have another clock in with no clock outs then Tuesday will start at midnight with 0 clock ins and once Employee 1 clocks out I now have -1 due to the 2 clock ins from the previous day. I need the 2 from Monday to roll over to the next column. I have the formulas for the days basically:

If (DayofWeek = 1 ) Then
{@InCount} - {@Out Count}

So once the day = 2 it will reset to 0. I tried removing the formula but that doesn't seem to correct this. I thought it would have been pretty easy to do but it is not turning out that way. Anyone have any ideas?

 

[Charliy]

If I understand correctly, you're counting the number of Ins and Outs for each day, so the total is the number who have not logged out. Seems like you need to add days total to the next day.

For instance If you have 10 In and 8 Out on Sunday, your net is 2. You would then add that net number to the Monday count, then the Monday net gets added to Tuesday, etc.

 

[Dross]

You are correct, just can't seem to make that work

 

[Charliy]

Don't you have separate formulas for each day of the week?

 

[Dross]

yes, I am using formulas, but I can't get the running total count to flip to the next day. I think it is because of the day of the week potion of the formula. I have them broken down below.


INITIALIZE SUNDAY
WhilePrintingRecords;
NumberVar RunningTotal_Sun;
RunningTotal_Sun := 0;

EVALUATE SUNDAY
WhilePrintingRecords;
NumberVar RunningTotal_Sun;
If ({Table.DayOfWeek} = 1 ) Then
RunningTotal_Sun:= RunningTotal_Sun + {@InCount} - {@Out Count}
Else
RunningTotal_Sun := RunningTotal_Sun

DISPLAY SUNDAY
WhilePrintingRecords;
NumberVar RunningTotal_Sun;
RunningTotal_Sun;


But once it flips to MONDAY, the formula initializes Monday as a new day and zero's out.

 

[Charliy]

Take a look at Evaluate After. You need to get each day's formula to evaluate in sequence, then you can initialize each new day's Running Total with the previous day's value.

Instead of using the shared variables, you could use actual Running Totals, one for each day of the week with an evaluaation formula to only count for that DOW. Then have a display formula that's basically (for Tuesday) {@NetMonday}+{#rtTuesday}

 

[Dross]

So scrap the 3 formulas I have? Create a new one called Monday and Set it as
If ({Table.DayOfWeek} = 1 ) Then
{@InCount} - {@Out Count}

Then have a running total formula of just counting the ins and outs for each day and call it RT Tuesday and then add my Monday formula to the Tuesday RT. I tried something like that before but I don't remember the hangup I had. I just want to make sure I am on the right path with your thoughts.

 

[Charliy]

What I'm suggesting is using Running Totals, not formulas, to count each day's Ins and Outs.

 

[Dross]

And that works for each day until the next day. I need to carry over the 2 from the day before to start out the next day.


So the report looks something like below. As you can see the 2 left over from Monday are not carrying over to Tuesday so when the 2 still clocked in from Monday clock out it shows a -2

MONDAY TUESDAY
12:00 1 0
1:00 2 0
2:00 4 -2
3:00 3
4:00 2
5:00 5
6:00 7
7:00 12
8:00 7
9:00 8
10:00 5
11:00 3
12:00 9
1:00 7
2:00 4
3:00 3
4:00 10
5:00 5
6:00 3
7:00 8
8:00 1
9:00 3
10:00 2
11:00 0
12:00 2

 

[Charliy]

Right. You use a Running Total to accumulate each days in's and outs. You then also have a formula for each day. You take your net from Sunday (formula) and add Monday's in's and out's(running total), that gives you Monday's net(formula) then you add Tuesday's in's and out's (running total) to get Tuesday's net (formula), etc.