Counting Combination Groupings Correctly

[CJP12711]

I have a list of procedures on my report. I'm using the below formulas, with a running total formula placed in the report footer (with labels). The data looks like this:

CathID Proc Name Billing Code
28563 Procedure 1 6452
28569 Procedure 1 6452
28570 Procedure 2 6791
28570 Procedure 3 6445
28575 Procedure 1 6452
28575 Procedure 4 7959

The report footer looks like this:
Procedure 1: 3
Procedure 2: 1
Procedure 3: 1
Procedure 4: 1

I'd like it to look like this:
Procedure 1: 2
Procedure 2: 0
Procedure 3: 1
Procedure 4: 1

If procedure 1 or 2 also has procedure 3 or 4 within the same case (cathID), I don't want to count procedure 1 or 2. Only procedure 3 or 4.

The formulas look like this:
//{@Procedure1}:
if {BILLING_CODES_PROC.BILLING_CODE} in ["6452"] then
{BILLING_CODES_PROC.BILLING_CODE} else
{@null}

//{@Procedure1/Procedure4}:
if {BILLING_CODES_PROC.BILLING_CODE} in ["6452", "7959"] then
{BILLING_CODES_PROC.BILLING_CODE} else
{@null}

Running Total for Procedure1 and Procedure 4:
Distinct Count on cathID with a formula in the evaluate section of distinctcount({@LHC/DEStent},{CATH.CATHID}) >1, reset never.

Any ideas on how to make sure if there is a combination of procedure 1 or 2 with procedures 3 or 4, that the counts are reflected accurately?

Thanks!
CJ

 

[CJP12711]

Forgot to mention that the report has 1 grouping level - cathID.

 

[lbass]

You are confusing things by not using formula names consistently. Also, where are the combined procedures?

To eliminate the "1's" and "2's" where they are in combination with others for the purpose of counting single 1's or single 2's, use an evaluation formula in the running total like:

//{#rt-proc1only}:
distinctcount({table.procedure},{cath.cathID}) = 1 and
{table.procedure} = 1

-LB

 

[CJP12711]

Sorry LB. I was trying to make it more simplistic, but ended up making it confusing. Here is a screen shot of what it looks like now. The section at the top is the details are with each procedure, code, and cathID listed. The are below in the footer is the running totals. You can see the LHC With Coronaries and Coronary Stent are not correct, as the Coronaries Only and Drug Eluting Stent and Pacers are incorrect as well.

I haven't gotten to the Botton 2 before the Pacers yet. I have just been trying to get the other ones working... Let me know what else you need.

 

[CJP12711]

Sorry - my attachment didn't seem to work.... Trying again.

 

[CJP12711]

I am blocked from uploading my screen shot. If you have an email address, I can forward it...

 

[lbass]

You cannot show us anything that is on YOUR C:\ drive, and it is against site policy to post e-mails. I don't really need to see this. Did you try what I suggested?

-LB

 

[CJP12711]

Yes - I did. It seems to work for one of them, but not the other. Here's what I'm seeing:

DETAILS:
28,563 LHC WITH CORONARIES 6452
28,569 LHC WITH CORONARIES 6452
28,570 CORONARIES ONLY 6791
28,570 STENT CORONARY FIRST VESSEL 6445
28,572 CORONARIES ONLY 6791
28,575 LHC WITH CORONARIES 6452
28,575 STENT DRUG ELUTING FIRST VESSEL 7959
28,578 STENT DRUG ELUTING FIRST VESSEL 7959
28,580 CORONARIES ONLY 6791
28,582 CORONARIES ONLY 6791
28,584 LHC WITH CORONARIES 6452
28,584 STENT DRUG ELUTING FIRST VESSEL 7959
28,585 INSERT PCMKR DUAL PERMANENT 6776
28,586 LHC WITH CORONARIES 6452
28,588 CORONARIES ONLY 6791

Report Footer:
Coronaries Only - 4
LHC With Coronaries - 3
LHC With Coronaries and Drug Eluting Stent - 2
LHC With Coronaries and Coronary Stent - 3
Coronaries Only and Coronary Stent - 1
Coronaries Only and Drug Eluting Stent - 3
LHC With Coronaries/Coronary Stent/Drug Eluting Stent - 0
Coronaries Only/Coronary Stent/Drug Eluting Stent - 0
Drug Eluting Stent and Coronary Stent - 3
Pacers 0

 

[lbass]

I don't know what I'm seeing here. How is each of the four running totals set up now?

-LB

 

[CJP12711]

For the LHC with Coronaries and Drug Eluting Stent (The Coronaries Only and Drug Eluting Stent and Coronaries Only with Coronary Stent are set up the same way - just swapped the billing code):

Formula:
//{@LHC/DEStent}:
if {BILLING_CODES_PROC.BILLING_CODE} in ["6452", "7959"] then
{BILLING_CODES_PROC.BILLING_CODE} else
{@null}

Running Total:
distinctcount({@LHC/DEStent},{CATH.CATHID}) >1

For the LHC with Coronaries and Coronary Stent:

Formula:
//{@LHC/CStent}:
if {BILLING_CODES_PROC.BILLING_CODE} in ["6452", "6445"] then
{BILLING_CODES_PROC.BILLING_CODE} else
{@null}

Running Total:
distinctcount({@LHC/CStent},{CATH.CATHID}) >1

For the LHC with Coronaries/Coronary Stent/Drug Eluting Stent: (again, the Coronaries Only/Coronary Stent/Drug Eluting Stent are set up the same, just with the swapped billing code)

Formula:
//{@LHC/DEStent/CStent}:
if {BILLING_CODES_PROC.BILLING_CODE} in ["6452", "7959", "6445"] then
{BILLING_CODES_PROC.BILLING_CODE} else
{@null}

Running Total:
distinctcount({@LHC/DE/CS},{CATH.CATHID}) >2

If a case has LHC/DEStent/CStent, then count it. The LHC/DEStent, the LHC, and the LHC/CStent should not reflect that count. That way we will avoid double counting. There are layers here. In order:

LHC/DEStent/CStent and Corns Only/DEStent/CStent
LHC/DEStent, LHC/CStent, and Corns Only/DEStent, Corns Only/CStent
Coronary Stent, DEStent, Pacers, LHC, Corns Only

If the case hits one of the top scenarios, it will not be in the lower ones. Hopefully that gives you what you wanted...

CJ

 

[lbass]

You are only showing me part of the running totals, and I don't see any here to reflect single cases. I don't know what you mean by layers. Each scenario should have its own running total, and when there are three values you are checking for, use: distinctcount({formula},{group}) = 3; for two, use = 2, for one, use = 1. Then you won't have the problem. Since you are using running totals in the report footer, they will count any instance that meets the criteria, regardless of whether another running total is also counting the value.

-LB

 

[CJP12711]

The ones I have listed above are the ones that aren't working. The others are fine, which is why I didn't show them. Do you want me to list out the ones that are working?