Retrieve data outside a range 1

[wyardjeff]

Hello, Our sales guys want me to write a report that returns the models that sold for more or less than comparable units. I am using crystal 8.5 quering an access 97 database.
The data looks like:
Year model # of cycles Sale price
1997 5p 45000 75.00
1998 4 20000 200.00
2000 6p 2500 120.00
1997 5p 7000 100.00
1997 5p 44000 120.00
1997 5p 6000 150.00
The report should return something like:
1997 5p 45000 75.00
1997 5p 44000 120.00
I have tried a select formula, year=year and model=model and cycle=((cycle>cycle*.9)or(cycle<cycle*1.1))and price=((price<price*.9)or(price>price*1.1))...that didn't work...so I tried grouping first by year then by model then running a select formula: cycle=((cycle>cycle*.9)or(cycle<cycle*1.1))and price=((price<price*.9)or(price>price*1.1)). I am lost at what to try next. Any guidance would be greatly appreciated.

Thank you
Jeff G.

 

[synapsevampire]

Perhaps if you described the requirement and provided expected output rather than what you tried that doesn't work it would be more clear.

I'll assume that the requirement is to show those cycles which sold for other than the average price?

If that's the case, then you need to know the average price for comparison, so group by whatever your criteria is ( year then by model?), place the price in the details field, right click it and select Insert-Summary->Average.

Now you have a field available for use as the average in the Group Selection Formula.

In essence you'll have something like the following in the Report->Edit Selection Formula->Group:

{Orders.Order Amount} <> Average ({Orders.Order Amount}, {Orders.Model)

If this does not work for you, please post your expected output from your previously posted example data.

-k

 

[wyardjeff]

Sorry about including my wrong solution. The result I am looking for isn't an average of the whole model line, It is a variance when two of the same year/model have roughly the same usage but widely varying price. I would like exclude models from the report that have been priced close to same price of like units or that have nothing to compare to.

The data in access looks like:
Year model # of cycles Sale price
1997 5p 45000 75.00
1998 4 20000 200.00
2000 6p 2500 120.00
1997 5p 7000 100.00
1997 5p 44000 120.00
1997 5p 6000 150.00

The report I am trying to write should return something like:
1997 5p 45000 75.00
1997 5p 44000 120.00


Thanks again
Jeff G.

 

[synapsevampire]

How would the report know that the aprox 45000 price isn't the one most common since there are 2 near this range and 2 near the 7000 range?

The lowest with more than one entry are considered the most likely?

One means to assist in this might be to sort by the cycles in ascending order within the group and use the previous function to test for sane values, as in:

table.year = previous(table.year)
and
table.model = previous(table.model)
and
table.price*.8 > previous(table.price)

This would show all units that are > 20% of the price than the previous value.

Or you might use a means to determine the MODE average in a subreport and pass it back to the main report for use in your formula.

I really can't understand how the report will know which is the proper value to look for exceptions from otherwise.

-k

 

[wyardjeff]

You are right. SORRY. I goofed on my desired output, it should be:
1997 5p 45000 75.00
1997 5p 44000 120.00
1997 5p 7000 100.00
1997 5p 6000 150.00
Because all four units are more than 20% different. I am trying what you suggested. My problem is there are 60000 to 100000 entries a month and I somehow have to distill it down to a 10 or even a 100 page report.

Thank you for your time
Jeff