Running total, but can't use a running total 1

[Dross]

I have a list of clients with a value and date sorted by name and then date order. I need to count the patients each time that value goes above 170, but only AFTER it is under 170 at least once. So if they are 300,300,300…I exclude them, but if it is 300,166,300,300 I would count the last 2 300 counts. Look at the example below, start with Alle-XXXXX, Sher-XXXXX. The numbers start out at 150, then hits 190 and stays till 160. That has to count as 8, not count the 160 and then count the 175,188,188,172, and 173. After that client, it would reset for the next one and won’t start counting until it gets to the 159 mark since it was never below 170 until then, and then start counting the 215 and 259 and 292.
Then it has to reset and count the next client and then after I need to be able to add the total in a department group. I put an example of the data below. Any help would be appreciated. I tried several ways by flagging and also by doing a 3 formula running total but I can’t get it to come out.



Alle-XXXXX, Sher-XXXXX 150 7/24/11 8:23 PM
Alle-XXXXX, Sher-XXXXX 190 7/24/11 9:10 PM
Alle-XXXXX, Sher-XXXXX 200 7/24/11 10:07 PM
Alle-XXXXX, Sher-XXXXX 222 7/24/11 11:11 PM
Alle-XXXXX, Sher-XXXXX 229 7/25/11 12:06 AM
Alle-XXXXX, Sher-XXXXX 206 7/25/11 1:49 AM
Alle-XXXXX, Sher-XXXXX 206 7/25/11 3:07 AM
Alle-XXXXX, Sher-XXXXX 180 7/25/11 5:24 AM
Alle-XXXXX, Sher-XXXXX 179 7/25/11 6:33 AM
Alle-XXXXX, Sher-XXXXX 160 7/25/11 8:13 PM
Alle-XXXXX, Sher-XXXXX 175 7/25/11 10:17 PM
Alle-XXXXX, Sher-XXXXX 188 7/26/11 12:02 AM
Alle-XXXXX, Sher-XXXXX 188 7/26/11 1:41 AM
Alle-XXXXX, Sher-XXXXX 188 7/26/11 2:24 AM
Alle-XXXXX, Sher-XXXXX 172 7/26/11 3:25 AM
Alle-XXXXX, Sher-XXXXX 173 7/26/11 5:02 AM
Alle-XXXXX, Sher-XXXXX 140 7/26/11 6:14 AM
Cord-XXXXX, Kris-XXXXX 297 7/1/11 8:17 PM
Cord-XXXXX, Kris-XXXXX 304 7/1/11 9:18 PM
Cord-XXXXX, Kris-XXXXX 307 7/1/11 10:04 PM
Cord-XXXXX, Kris-XXXXX 295 7/1/11 11:04 PM
Cord-XXXXX, Kris-XXXXX 301 7/2/11 12:25 AM
Cord-XXXXX, Kris-XXXXX 251 7/2/11 1:07 AM
Cord-XXXXX, Kris-XXXXX 228 7/2/11 3:33 AM
Cord-XXXXX, Kris-XXXXX 211 7/2/11 3:58 AM
Cord-XXXXX, Kris-XXXXX 189 7/2/11 5:04 AM
Cord-XXXXX, Kris-XXXXX 159 7/2/11 6:04 AM
Cord-XXXXX, Kris-XXXXX 141 7/2/11 6:52 AM
Cord-XXXXX, Kris-XXXXX 119 7/2/11 9:00 AM
Cord-XXXXX, Kris-XXXXX 119 7/2/11 9:01 AM
Cord-XXXXX, Kris-XXXXX 215 7/2/11 10:35 AM
Cord-XXXXX, Kris-XXXXX 159 7/2/11 11:28 AM
Cord-XXXXX, Kris-XXXXX 259 7/2/11 11:29 AM
Cord-XXXXX, Kris-XXXXX 292 7/2/11 12:45 PM
Gonz-XXXXX, Mari-XXXXX 190 7/5/11 6:08 AM
Gonz-XXXXX, Mari-XXXXX 203 7/6/11 12:43 PM
Gonz-XXXXX, Mari-XXXXX 213 7/6/11 2:09 PM
Gonz-XXXXX, Mari-XXXXX 235 7/6/11 4:09 PM
Gonz-XXXXX, Mari-XXXXX 233 7/6/11 5:30 PM
Gonz-XXXXX, Mari-XXXXX 233 7/6/11 5:31 PM
Gonz-XXXXX, Mari-XXXXX 233 7/6/11 5:32 PM
Gonz-XXXXX, Mari-XXXXX 233 7/6/11 5:33 PM
Gonz-XXXXX, Mari-XXXXX 280 7/6/11 6:35 PM
Gonz-XXXXX, Mari-XXXXX 396 7/10/11 7:06 PM
Gonz-XXXXX, Mari-XXXXX 267 7/11/11 12:12 AM
Gonz-XXXXX, Mari-XXXXX 267 7/11/11 12:14 AM
Gonz-XXXXX, Mari-XXXXX 255 7/11/11 1:04 AM
Gonz-XXXXX, Mari-XXXXX 255 7/11/11 1:06 AM
Gonz-XXXXX, Mari-XXXXX 229 7/11/11 2:12 AM
Gonz-XXXXX, Mari-XXXXX 228 7/11/11 6:14 AM
Gonz-XXXXX, Mari-XXXXX 182 7/11/11 8:15 AM

 

[briangriffin]

The three formula approach should work for you - the detail formula would be:

whileprintingrecords;
booleanvar v_startcounting = false;
numbervar v_count = 0;

if {yournumberfield} < 170 then v_startcounting := true;

if v_startcounting = true
and {yournumberfield} >= 170 then v_count := v_count + 1;

v_count

Reset formula goes in the group header:

whileprintingrecords;
booleanvar v_startcounting := false;
numbervar v_count := 0;

Then display the count in the group footer:

whileprintingrecords;
numbervar v_count;
v_count

You would need another formula if you want to display the grand total, but this should get you most of the way there.

 

[Dross]

That is how I originally had it, but it will count all that is above 170. I need the ones above 170 only after it went below 170. It also has to reset for each customer so a {field} = Previous ({field}) will probably be needed as well

 

[briangriffin]

I grouped on the first field, so for Alle-XXXXX my total is 6 - it doesn't start counting until the 160 value, and there are 6 values of 170 or higher after that (there are 7 records, but one of them is less than 170).

What happens in your report?

BTW, my personal preference is to use next and previous sparingly - you can usually do the same thing with variables, and have a little more control over it.

 

[briangriffin]

My count is off by one because the first row came into the report as column headers, but the formulas should still work - my total for the first group is now 13, which I believe is the figure you came up with.

 

[lbass]

Try this:

whileprintingrecords;
numbervar cnt1;
numbervar cnt2;
if {table.value} < 170 then
cnt1 := 1 else
cnt1 := cnt1;
if cnt1 = 1 then
if {table.value} >= 170 then
cnt2 := cnt2 + 1;
if {table.value} < 170 then
cnt2 := 0;
cnt2

-LB

 

[Dross]

Thanks fot the help. The 1st client total should be 14. 2nd should be 3 and the 3rd client total should be 0 since he never got into the uder 170 range to begin with.

lBass, where do I put this formula?

 

[lbass]

Create it in the field explorer->formula->new and add it to the section containing your fields as shown above (detail?).

-LB

 

[Dross]

I think it is close but not there yet. I was working on it last night and it still isn't counting the correct ones. This whole project is a nightmare. They didn't think about how to report any of the data until after they put the whole application into production.

 

[Dross]

So basically I need the counter to start once the client hits under 170. Then start counting any instance when they are above 170 and reset for each client and be able to total at a department level. I am grouping by Department and not client, but I can group by client if it is needed to make the formulas work. Tall order and I am banging my head against the wall.

 

[lbass]

Please show how you implemented my formula--I did test this. If you have a group that you want to reset on, you have to add a reset formula like this to the group header:

whileprintingrecords;
numbervar cnt1 := 0;
numbervar cnt2 := 0;

If you want it reset per client, then yes, add a group per client and add this reset to the client group header. You can suppress the group sections.

If it is not working as expected, show the formula you used and show a sample of data and the results returned by the formula.

-LB

 

[Dross]

I see where my issue is. I have it resetting and counting correctly, but instead of showing the total by client, I have a department group I need to total on. So the counts per client looks right, now I just need to take the counts by client and sum them up by the department group. Will I be able to do that with another formula?

 

[lbass]

But what is it you are summing??? Let's say you have data like the following for two clients:

abc 175 0
abc 165 0
abc 180 1
abc 300 2
abc 200 3
abc 150 0
abc 177 1
abc 153 0

cde 165 0
cde 200 1
cde 144 0
cde 175 1
cde 180 2
cde 200 3
cde 135 0
cde 221 1
cde 441 2
cde 120 0
cde 180 1

What would the sum be for these two clients?

-LB

 

[Dross]

Assuming both were in the same department, it would be abc = 4, cde = 7 so the department total will be 11

The parameters are start counting after the first time they are below 170 and go above 170.

 

[lbass]

Is there any reason to reset the formula to zero then when the value is under 170?

-LB

 

[Dross]

Not for the client. Once the counter starts the initial time it goes above 170 after being below 170 it should only reset on a new client but I need to add the total for each client in the department group. +

So using your example it would be something like:

DEPARTMENT 1
abc 175 0
abc 165 0
abc 180 1
abc 300 2
abc 200 3
abc 150 0
abc 177 4
abc 153 0

cde 165 0
cde 200 1
cde 144 0
cde 175 2
cde 180 3
cde 200 4
cde 135 0
cde 221 5
cde 441 6
cde 120 0
cde 180 7

DEPARTMENT 1 TOTAL: 11

Add 4 from the first client to the 7 of the second client and have a total of 11 for Department 1

 

[lbass]

Change this formula to:
whileprintingrecords;
numbervar cnt1;
numbervar cnt2;
if {table.value} < 170 then
cnt1 := 1 else
cnt1 := cnt1;
if cnt1 = 1 then
if {table.value} >= 170 then
cnt2 := cnt2 + 1;
if {table.value} < 170 then
cnt2 := cnt2;

Add this formula to the Client group footer:
whileprintingrecords;
numbervar cnt2;
numbervar cnt3 := cnt3 + cnt2;

Add this formula to the department group header:

whileprintingrecords;
numbervar cnt3 := 0;

Add this formula to the department group footer to display the department total:

whileprintingrecords;
numbervar cnt3;

-LB

 

[Dross]

Wow, that is awesome dude! That worked and I really appreciate it!