running total reset on change of TWO variables

[mwhalen]

I want to count the number of consecutive c's under the Absent Flag BUT after EACH incident of the y. I'm thinking I want to reset after each grouped ID# and after each incident of y. I'M STUCK! How do I do this??


ID# 111
Date Absent Flag
Mar 1 y
May 10 y
May 11 c
May 12 c
Sept 3 y

ID# 112
Date Absent Flag
Jan 3 y
Jan 4 c
Jan 5 c
Jan 6 c
April 28 y
April 29 c
April 30 c

ID# 113
Date Absent Flag
Feb 22 y
Mar 10 y
May 13 y
May 14 c
May 15 c
July 31 y
November 3 y

 

[synapsevampire]

The example dtaa helps, if you had shown the expected results it would been perfectly clear, but I think I have it.

I'll assume that this data is at the detail level:

Create a Running Total and select the Absent Flag field to Count, in the evaluate use a formula place {table.absentflag} = "C", and in the reset place {table.absentflag}

Now you can place this RT in the details and get a running total.

Again, it's hard to guess because you've given no indication of what you want.

-k

 

[mwhalen]

Thanks - what I would want to see is set out below:

ID# 111
Date Absent Flag Running Total
Mar 1 y
May 10 y
May 11 c
May 12 c 2
Sept 3 y

ID# 112
Date Absent Flag
Jan 3 y
Jan 4 c
Jan 5 c
Jan 6 c 3
April 28 y
April 29 c
April 30 c 2

ID# 113
Date Absent Flag
Feb 22 y
Mar 10 y
May 13 y
May 14 c
May 15 c 2
July 31 y
November 3 y

 

[mwhalen]

Okay I've tried that but here's a problem - when the last absent flag of ID#1 is a "C" and the first absent flag of ID#2 is a "C" I will get the following:
3102
3102 2003/11/25 Y 0
3102 2003/11/26 C 1

3119
3119 2003/12/04 C 2
3119 2003/12/04 Y 0

3170
3170 2003/03/26 Y 0
3170 2003/03/27 C 1

3213
3213 2004/01/26 Y 0
3213 2004/01/27 C 1
3213 2004/02/04 C 2
03213 2004/02/05 C 3
03213 2004/02/09 C 4
03213 2004/02/10 C 5

 

[synapsevampire]

OK, change the reset to:

Use a formula:

previous({table.absentflag}) <> {table.absentflag}
or
previous({table.id}) <> {table.id}

-k

 

[mwhalen]

Sorry, those options still don't give me the correct totals. I need it to reset on BOTH conditions at the same time.

 

[lbass]

Set your evaluation formula to:

{table.flag} = "C"

Set your reset formula to:

{table.flag} = "Y" or
{table.ID} <> previous({table.ID})

-LB

 

[mwhalen]

but I don't want it to reset on one OR the other I want it to reset on BOTH

 

[butkus]

Change the OR to an AND in lbass's reset formula"

{table.flag} = "Y" and
{table.ID} <> previous({table.ID})

that way both conditions must be met.

James

 

[mwhalen]

I've tried that and unfortunately it doesn't work.

 

[lbass]

You must not have tried my suggestion:

Set your evaluation formula to:

{table.flag} = "C"

Set your reset formula to:

{table.flag} = "Y" or
{table.ID} <> previous({table.ID})

This will reset the count as you wish--as you showed in your example--whenever a "Y" occurs within a group or when a new group starts.

-LB

 

[synapsevampire]

Perhaps the report isn't grouped as we assume, the examples make sense to me...

-k

 

[mwhalen]

It's grouped on ID.

 

[mwhalen]

Okay, it did work - my apologies. Thanks!

Now, I want to suppress details and groups where the running total is not equal to 5. But then how do I display the total count of occurrences of 5's at the footer?

 

[lbass]

The only way you can do this with running totals is to use a subreport. Also, I'm assuming you want to display and count those groups where there is a running total of at least 5, not only = 5.

First, save your report under a different name, e.g., "rtsubrpt" and then reopen your original report. Then go to insert->subreport and choose "rtsubrpt". Place this in the report header. Then go into the subreport and create two formulas:

//{@five} to be placed in the subreport detail section:
shared stringvar x;
if {#yourrt} = 5 then //where {#yourrt} is your running total
x := x + totext({table.ID},"000") + ", " else
x := x;

//{@display} to be placed in the subreport report footer:
shared stringvar x;

Suppress all subreport sections, but do not suppress the report header in the main report.

Then in the main report, go to format->section->group header (and then detail and group footer sections)->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar x;
instr(x,totext({table.ID},"000")) = 0

This will suppress all but those groups in the main report which have a running total >= 5. To get a count of the groups which meet this criterion, create a formula and place it in the report footer:

whileprintingrecords;
shared stringvar x;
ubound(split(x,","))-1;

-LB