Cross-Tab - Difference between summarized fields

[7wheel]

I'm using CR 10 with an XP workstation.

I have a cross-tab report grouped by year; e.g. 2004 and 2005; then it's grouped by physicians.

I have summarized fields for # of patient visits, charges, and average charge per visit.

I need to show the difference between 2004 & 2005 for # of visits, charges, and average charge per visit.

Any ideas?

 

[lbass]

So your row fields are year and physician?

-LB

 

[7wheel]

Yes that's correct. Year and physician are row fields.

# of patient visits, charges, average charge per visit are column fields and are sumarized for each physician.

7wheel

 

[lbass]

You will need to remove year as a row. Then create formulas like this:

//{@2005 Count}:
if year({table.date}) = 2005 then 1 //or {table.count}

//{@2004 Count}:
if year({table.date}) = 2004 then 1 //or {table.count}

//{@2005-2004 Count Diff}:
{@2005 Count}-{@2004 Count}

//{@2005 Charges}:
if year({table.date}) = 2005 then {table.charge}

//{@2004 Charges}:
if year({table.date}) = 2004 then {table.charge}

//{@2005-2004 Chg Diff}:
{@2005 Charges} - {@2004 Charges}

Add these as your summary fields (summing each one) and then go to the customize style tab and choose "Horizontal" and "Show Labels" for the summary display. Then go back to the main crosstab screen and add {@2005 Charges} and {@2004 Charges} again, only this time, choose "Weighted Average". For {@2005 Charges}, choose {@2005 Count} as the field to weight with. Repeat for {@2004 Charges}, choosing the {@2004 Count} field as weight.

To get the difference for the average charges, right click on the {@2005 Charges} average->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar 2005chg := currentfieldvalue;
false

Then select the average for 2004 (weighted average of {@2004 Charges})->format field->suppress->x+2 and enter:
whileprintingrecords;
currencyvar 2004chg := currentfieldvalue;
false

Then create a formula {@Average Charge Diff}:
whilereadingrecords;
0

Insert this as a final summary and right click on it->format field->display string->x+2 and enter:

whileprintingrecords;
currencyvar 2005chg;
currencyvar 2004chg;
totext(2005chg-2004chg,2)

Finally, edit the labels of each summary column to meet your needs.

-LB

 

[7wheel]

LB -- Thanks for your response. The count and count diff formulas are calculating correctly.

However, the average charge diff is not correct in every row. If there are values for both 2004 & 2005, then the charge difference is correct.

However, if the weighted average for 2005 is $0, then the average charge difference compared to year 2004 is not correct.

It looks like it's taking the 2005 weighted average charges from the row above it and subtracting the 2004 weighted average charges. That difference is populating into the average diff column.

Any assistance you can offer is appreciated.
SF

 

[lbass]

That's exactly what it is doing. What should it be doing? Aren't you looking for the difference between the two average charges? Can you show an example of what you are getting and what you want to get?

-LB

 

[7wheel]

LB - Here's an example of what I'm getting.

Columns:
1 2 3 4 5 6 7 8
Phy #1 8 1 113901 21796 14238 21796 7558
Phy #2 37 0 407708 0 11019 10777
Phy #3 23 19 188519 215466 8196 11340 3144

Columns represent:
1= physician id
2= 2004 # of visits
3 = 2005 # of visits
4 = 2004 total charges
5 = 2005 total charges
6 = 2004 weighted average charge/visit
7 = 2005 weighted average charge/visit
8 = Difference between 2005 & 2004 average charges

The diff is correct for phy #1 & 3; but not for phy #2. It should be 11019 and not 10777. If you take 21796 from row above and subtract 11019, the result is 10777. Might be coincidence; but it's happening on any row with no charges in the 2005 total charges column.

Notice for phy# 2, 0 for 2005 # of visits; 0 for total charges. There's no 0 in column #7 for weighted average charge/visit; but there should be.

Thanks for helping me with this. It's a report that will become the basis for several other physician comparison reports. I'm still new to this site so thanks for your patience.

SF

 

[lbass]

You need to address the null issue. Try going to file->report options and check "Convert nulls to default values" (and maybe also "convert other values to default"). Otherwise, if you don't want to do that, please explain what the field being used for the weighting is, and whether it can be null. If it can be, then try creating a formula:

if isnull({table.wgtfield}) then 0 else {table.wgtfield}

...and use that as the weight field. Not sure about this.

-LB