Running total formula 1

[FatEric]

Hi everyone.

I'm trying to make a running total formula work, but not getting there...

In my report, I have detail lines containing a date and a number (0 or 1) if a person is sick or not on that date. The details are grouped by personal number. Now what I'm trying to do is to count the frequency of sickness (so not the number of days). I will give some example codes:

nr 68
01/01/2008 0 --> here I want 0
02/01/2008 1 --> here I want 0
03/01/2008 1 --> here I want 1
04/01/2008 0 --> here I want 0
07/01/2008 0 --> here I want 0
08/01/2008 1 --> here I want 1
...
nr 72
01/01/2008 0 --> here I want 0
02/01/2008 0 --> here I want 0
03/01/2008 0 --> here I want 0
04/01/2008 0 --> here I want 0
07/01/2008 1 --> here I want 1
08/01/2008 0 --> here I want 0

What I tried to do was the following... I make a formula field with the following:

IF ONFIRSTRECORD THEN
0
ELSE
IF {T6003.TPZIEK} = 1 AND NOT ONLASTRECORD THEN
IF NEXT({T6003.TPZIEK}) = 1 THEN
0
ELSE
1
ELSE
IF {T6003.TPZIEK} = 1 AND ONLASTRECORD THEN
1
ELSE
0

But the problem is that when the field has a 1 value on the last detail record of the group and the first detail record of the next group also contains a 1, the last record gives a 0 instead of a 1.

Can somebody help me with this. If you need more info, more examples, ... please ask.

Thanks alot,
FatEric

 

[lbass]

Change the evaluation formula to:

(
onlastrecord or
{table.personalnumber} <> next({table.personalnumber}) or
NEXT({T6003.TPZIEK}) <> 1
) and
{T6003.TPZIEK} = 1

In the future it would help if you identified your date format--at first I thought you were using "MM/dd/yyyy" and looking at whether people had any sick dates within a month.

-LB

 

[FatEric]

Thanks lbass,

The formula now works and I see the right number of frequency. Only when it is zero, it does not show anything (I want to see 0). It also only is possible in the footer of the group. But is there a way to put it in the header of the group. I have used summary's to show totals in the group headers. So I made of formula field where the formule I made with your changes. That field I show in the report and shows the correct values. But I seem to not be able to make a summary on that field. But when I leave out the next and previous codes, I can...

I'm using CR Prof 9

And the dates are indeed in "dd/MM/yyyy" format. I'm Belgian so for me it's normal to use that format. Sorry about that...

Thanks, FatEric

 

[lbass]

If zeros are not showing, then you either used "suppress if zero" or more likely, you are running into a null somewhere. Can either the field you are counting or the {T6003.TPZIEK} field be null? To see if nulls are occurring, create a formula:

if isnull({#yourrunningtotal}) then
0 else
{#yourrunningtotal}

You will not be able to get a summary of the results in the group header since the running totals are calculated in a later section. I don't think there is a way of using a conditional formula, since the desired result involves a comparison across records. This means your only option would be to add a subreport (based on the current report) to the group header that is linked on the group, and that displays the summary in the group footer in the sub. Then you could suppress other sections within the sub. To do the calculation, just change the reset in a new running total to the group level, using the same evaluation formula.

Regarding the date formatting, a simple way to show your formatting is to use sample data where the day values are greater than 12.

-LB

 

[FatEric]

Hi lbass,

Thanks for your reply. It did explain a lot. Too bad I can't use the running total in the header. So I will redesign my report a little and put it in the footer.

I'm also sure that there are no NULL values in that field. Also "suppress if zero" is not ticked. "Show zero values as" is changed to "0". Is there something else I missed?

FatEric

 

[lbass]

Try using the formula I suggested to test for nulls.

-LB

 

[FatEric]

Hi lbass,

Thanks for every help. My report is now showing everything it has to show!! With your formula it did work.

Thanks very much,

FatEric