Median

[kinvie]

I am using Crystal Version 12.2.4 running against an Oracle database. I am pulling the complaints and then using a group summary to calculate the count of complaints by month. DistinctCount ({pat_evt_ah.event_id}, {pat_evt_ah.event_dt}, "monthly")

I have now been asked to calculate the Median for the last 12 months and place this on a graph. All of this is in a subreport of a larger report.

I can't get a running total to work since the field is already summarized. Anybody have some other thoughts to get this done?

Thanks

 

[Madawc]

You should be able to do a running total from the detail-line value for the whole year. Also Crystal will generate it for you: you seem to have used your own code which is not necessary. The use of Crystal's automated totals is outlined at FAQ767-6524.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You should be able simply to select median as the summary field for the chart.

-LB

 

[kinvie]

I tried placing the median on the graph and it is the median of the field and not the median of the distinct count of the field. I don't see how to place a median of a summarized field.

 

[lbass]

kinvie, yes, sorry--I wasn't thinking clearly. One way of approaching this would be to use a SQL expression to return the distinctcount, and then you would be able to use a median of this in the chart. Try creating the following:

(
select count(distinct "event_id")
from pat_evt_ah A
where {fn month(A."event_dt")} = {fn month(pat_evt_ah."event_dt")}
)

-LB

 

[kinvie]

LB -

When I use your expression the Crystal formula checker says the ) is missing. I have copies and recopied and get the same thing. I am using this in the formula editor as I can't seem to find the SQL expression builder in this version (we are newly upgraded...) Any other ideas for me?

MaDwc -

I am not sure I am following. The Discintcount that I pasted is a summary built using the SUMMARY located in the insert drop down. I just copied the expression, Crystal builds, I did not write this.

Thanks for your help I would love additional ideas if you have any. Thanks

 

[lbass]

The SQL expression editor should appear in the field explorer--unless you are also using a command in your report or you are using a stored procedure. You could create this as a command also.

-LB

 

[kinvie]

I am thinking our IT department must not have installed the SQL expression builder when we did the upgradeto Crystal 12 as it isn't where it used to be. In addition when I try to do the command I get that I can't do this as it cannot be combined with Business Views... UUGH how can finding the middle be so hard????

 

[lbass]

I don't have CR 2008, but the SQL expression editor is a standard feature of the field explorer, and I don't think it would have been a download issue. If you are using a Business View, that may be the reason--I'm not really familiar with this. If a Business View is really a view, then I'm guessing you should be building the subquery to return the distinctcount right into the view.

-LB