Adjusted Average?

[TMcCrillis]

I'd like to create an average in Crystal 8.5 however, I need the calculate to exclude any quantity of 0 or 1 from the average.
Part Days
1A 20
1A 37
1A 5
1A 1
1A 0
1A 3

I have an average calculated already which would give me an average of 11 - - What I'm looking for is 16.25 because we know that 0 and 1 days are exceptions to the reporting we're doing and only distort the data.
Suggestions?

 

[Turkbear]

Hi,
Probably other ways but one way might be to create a formula ( let's call it ValidNumber)
@ValidNumber

If  {Days} <2 
Then
'Bad Data'
 else
If  {Days} >1
Then
'Good Data'

Group on this formula and compute the Average on the Group Header or Footer

You can supress, if you want, the display of the 'Bad Data' group...







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

You could also do a running total that has a formula that excludes the cases you don't want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Why not just exclude them in the record selection formula?

{table.days} > 1

-LB

 

[TMcCrillis]

Thanks for the info. I can't exclude the records because I still need them for the subreport...I need to see all records for a serial number when the subreport(on demand) is opened.

 

[lbass]

Then insert a running total that does an average of {table.days}, evaluate using a formula:

{table.days} > 1

Reset never for an average at the report level, or reset on change of group for an average at a group level. The running total needs to be placed in the corresponding footer section (report or group).

-LB

 

[TMcCrillis]

I can't use a running total because the data that I need to pull the adjusted average from is not stored in a table. It is part of another formula. Can I still use a running total?

 

[lbass]

Do you mean that days is based on a formula? You will have to show the contents of the formula and of any nested formulas.

-LB

 

[TMcCrillis]

//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {V_ITEM_HISTORY.DATE_HISTORY}, next({V_ITEM_HISTORY.DATE_HISTORY}));

if {V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' and
{V_ITEM_HISTORY.SERIAL_NUMBER} = next({V_ITEM_HISTORY.SERIAL_NUMBER}) then
add := add + diff else

if {V_ITEM_HISTORY.SERIAL_NUMBER} <> next({V_ITEM_HISTORY.SERIAL_NUMBER}) and
{V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' then
add := add + (currentdate-{V_ITEM_HISTORY.DATE_HISTORY});
add


This is the formula for the date calculation. I need to get an average based on this field. However I need to "ignore" anything less than 2 days. If it's less than 2 days, that means it was built to order and not true inventory. I don't want build to order items to skew the results.

 

[lbass]

I explained to you how to do an average in thread149-1381890. All you need to do is adjust the formula above to:

//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {V_ITEM_HISTORY.DATE_HISTORY}, next({V_ITEM_HISTORY.DATE_HISTORY}));

if {V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' and
{V_ITEM_HISTORY.SERIAL_NUMBER} = next({V_ITEM_HISTORY.SERIAL_NUMBER}) [red] and
diff > 1[/red] then
add := add + diff else

if
[red](
nextisnull({V_ITEM_HISTORY.SERIAL_NUMBER}) or
{V_ITEM_HISTORY.SERIAL_NUMBER} <> next({V_ITEM_HISTORY.SERIAL_NUMBER})
)[/red] and
{V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' [red] and
currentdate-{V_ITEM_HISTORY.DATE_HISTORY}) > 1 //not sure about this--
//depends on YOUR logic [/red]
then
add := add + (currentdate-{V_ITEM_HISTORY.DATE_HISTORY});
add

I also added a phrase to allow for the last record in the report. The average is calculated as noted in the other thread. You should probably respond in that thread with any problems related to calculating the average.

Note that you wasted everyone's time until this point by not explaining the situation thoroughly in your first post. Whenever you are referencing a formula, you must show the contents, as it affects the solution. The use of "next" and "whileprintingrecords;" in the formula means that it can't be used in a running total.

-LB

 

[TMcCrillis]

Thank you for your help. I appreciate it. It was not my intention to "waste everyone's time". As I mentioned, I'm very new to this and am fighting my way through as I go.