Filtering based on standard deviation

[jarmoore]

I'm trying to create an "outlier" filter for a Crystal Report. We have some values that are throwing off averages and want to filter them out. Using standard deviation to filter out the upper end of values seemed like a good solution. In my select formula I originally had the following:

(@TotalAge) > (Average(@TotalAge) + (StdDev(@TotalAge) * guiFilterOutlierAmount.SelectedValue))

(guiFilterOutlierAmount is a drop down list with the amount of standard deviations to filter from, 0-3)

Using this in the select formula, I kept getting the error "The function cannot be used because it must be evaluated later." My next solution was to create a SQL Expression that did the Average and StdDev calculations inside.

(@TotalAge) > (%TotalAgeMean + (%TotalAgeStdDev * guiFilterOutlierAmount.SelectedValue))

This worked fine but did not allow me to do dynamic groupings like I needed. It was simply a sub query that calculated the standard deviation for a whole table. I've also experimented with using a stored procedure that calculates the standard deviation but still can't get around the dynamic grouping problem that crystal handles. I'm running out of ideas now, any thoughts or suggestions would be appreciated.

 

[lbass]

Are you saying you want to calculate the standard deviation at the group level? You can build in a faux group in the SQL expressions.

-LB

 

[jarmoore]

Yep, that is what I need to do. The problem is that I have dynamic groups based on a formula. My SQL expression would need to look like:

(
SELECT STDEV(DATEDIFF(DAY, mod_appMilestones.AppReceivedDate, mod_appMilestones.PolicyToAgentDate))

FROM mod_appMilestones

GROUP BY {@UserGroup}
)

Trying to use my group formula in the SQL Expression throws the error, "Syntax error or access violation". I also tried using a parameter and passing in the group from .NET but it tells me "No value given for one or more required parameters." Is it possible to use formulas in SQL expressions or do you know another way around this?

 

[lbass]

What is the content of your group formula (and any nested formulas)? Is the SQL expression currently accepting the summary without the group by?

-LB

 

[jarmoore]

The group formula has a parameter that is passed in through .NET to determine which grouping was selected by the user.

Select Case {?UserGroup1}
Case "Agent"
If IsNull({@Name of Agent}) Then
formula = " Unassigned"
Else
formula = {@Name of Agent}
End If

Case "AgentCo"
If IsNull({aw_entity.CORPNAME}) Then
formula = ""
Else
formula = {aw_entity.CORPNAME}
End If
.
.
.
etc.

Any nested formulas basically just handle formatting. @Name of Agent for instance is simply:
formula = ProperCase({aw_entity.NAME})

Yes, the SQL expression works just fine without the group by. The filter in the select formula works as expected. I just need it to do the summary based on the dynamic grouping, otherwise it gets rid of outliers for the whole table.

 

[lbass]

I'm not sure how the integration with .net works, but you can't use parameters in SQL expressions--you would have to use a command. Is that feasible? I could take you further, re: how to set up the subquery, but I'm not sure how you would access the parameter. Ordinarily you would create the parameter within the command.

-LB

 

[jarmoore]

I'm thinking a SQL command will work. I can create the parameter in the command and set it in .NET just like any other parameter (hopefully). I will keep you updated. Thanks for the suggestions.

 

[jarmoore]

Another thing I just thought of, would it be possible to set that parameter equal to the formula? For instance:

{?UserGroupSet} = {@UserGroup}
Or
SetParameterValue("{?UserGroupSet}", "{@UserGroup}")

Not sure of the syntax but something like that done in Crystal. That would keep me from having to pass in the group from .NET and it would just happen in Crystal when the page loads.

 

[lbass]

You can't use formulas within a command--you must recreate the functionality of the formula.

-LB

 

[jarmoore]

Right, but would it be possible to pass in what the formula returns to my SQL command parameter, essentially doing the same thing?