Median of Summary Value 1

[jenschmidt]

I've looked at other posts regarding finding the median of a summary, but the things I've tried still won't work. Please help!

Group 1 : Specialty of the Provider
Group 2 : Last Name of the Provider
Summary : Charges

Example:
GH1 Family Medicine
GH2 Smith $100,258
GH2 Jones $ 45,485
GH2 Adler $214,985
GH2 Kurth $ 89,476

The dollar amounts listed are the sums of all the charges for each provider. The middle summary for GH1 Family Medicine would be $89,476.

The reason I need the Median is because I want to apply it on a graph. The graph is for each Specialty, on change of Last Name, showing values Benchmark (which is the same for all providers in the specialty) and sum of Charges (there is a TopN sort on sum of Charges showing All, not just the Top 10 or Top 20). Median would also be the same for all providers in the specialty.

I've tried to create a subreport that passes the summary for each provider to the main report and then do a median on the main report using that value. But, when I create the shared variable, I get zero's on the main report, no matter where I place the subreport. I may have done the variables wrong, but I think I followed the syntax correctly.

I'm using Crystal Reports v9.0. I'm open to any suggestions that will solve this issue!
Thank you in advance!!!!

jennifer.giemza@uwmf.wisc.edu

 

[MColeman]

"But, when I create the shared variable, I get zero's on the main report, no matter where I place the subreport."

Did you know that to access the value of the shared variable returned from a subreport, you need to use a lower group?

For example, if the subreport is in group footer 1, the value of the shared variable in group footer 1 will be empty. The value of the shared variable in this example would be available in group footer 2.

 

[jenschmidt]

I put the subreport in GF1 and the formula (created in the main report that references the info I need out of the subreport) in GF2 and I still get zero's.

Variable declared in subreport:
NumberVar rvumed := Sum({@Charges}, {@Last Name});
rvumed;

Variable referenced in main report:
Shared NumberVar rvumed;
rvumed;

Thanks for the thoughts!

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

It's possible to calculate the median, but I'm not sure you will be able to chart on it directly in CR (although I'm not familiar with 9.0).

In your example, which by the way does not show the topN sort, it looks like you are rounding to the next highest count within the group in determining the "middle" value when there are an even number of groups, as in:

GH1 Family Medicine count
GH2 Adler $214,985 1
GH2 Smith $100,258 2
GH2 Kurth $ 89,476 3
GH2 Jones $ 45,485 4

where the record count at the median pt = 2.5 so round up to 3 and use that result. In another recent thread, the poster wanted to average the values for records 2 and 3 to get the median.

Anyway, you can get the median (using what I think is your approach) with a formula like:

if {#cntwingrp} = round(distinctcount({table.provider}, {table.specialty})/2,0) then sum({table.charges},{table.provider}) else 0

//where {#cntwingrp} is a running total distinctcount of {table.provider}, evaluate for each record, reset on change of Group 1 (specialty).

This would give you the median at the provider group level.

Or, if you wanted to display it at the Group 1 (specialty) footer level, you could create a variable:

//{@medianx} for the group 2 header:
whileprintingrecords;
numbervar medianx;

if {#cntwingrp} = round(distinctcount({table.provider}, {table.specialty})/2,0) then
medianx := sum({table.charges},{table.provider})

//{@display} for the Group 1 footer:
whileprintingrecords;
numbervar medianx;

Again, I'm not sure you can chart on this in CR. If not, you could suppress all but the group 1 footer (where the group name and median will be displayed if you use the variable method), export the report to Excel, create a chart there, and then copy it into the CR report header or footer.

-LB

 

[lbass]

Actually, let me amend that. Assuming you have applied a topN for the Grp 2 sums, then the following works correctly:

whileprintingrecords;
numbervar medianx;

if remainder(distinctcount({table.provider},{table.specialty}),2) = 0 then
(if {#cntwingrp} = (distinctcount({table.provider}, {table.specialty})/2) + 1 then
medianx := sum({table.charges},{table.provider}));

if remainder(distinctcount({table.provider},{table.specialty}),2) <> 0 then
(if {#cntwingrp} = round(distinctcount({table.provider}, {table.specialty})/2,0) then
medianx := sum({table.charges},{table.provider}));
medianx;

//{@display} for the Group 1 footer:
whileprintingrecords;
numbervar medianx;

-LB

 

[jenschmidt]

THANK YOU!! THANK YOU!! THANK YOU SO MUCH!! I saw the other post with the user who ended up doing an average of records 10 and 11 but I just wasn't understanding the formulas. Thanks for helping me out!!

One last thought - would it be possible to pass the medianx variable through a subreport in order to graph it at the specialty level? I'm currently using a subreport to pass the value through so that the median is reflected on every GH2 instead of the first few showing 0. But that still won't let me graph it.

Any thoughts would be appreciated!
Thanks again!

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

I don't think that using a subreport will help solve the charting issue. I might be wrong, but I don't think you can chart on variables, at least not in 8.0. I tested exporting data to Excel, creating the chart there, and then copying the chart back into CR, and this worked fine. That's the only real possibility I can think of. Perhaps someone else will have a different charting solution.

-LB