Need Help with Tricky Record Selection Formula

[dfaith]

I'm hoping someone can help me - I'm supposed to print the past 12 month's of sales quantities for each of our 4000 clients if the last full month's quantity sold is greater than 40% of the average quantity sold to that customer in the previous 90 days.

I do have Crystal 9, but I also have many clients who do not have 90 days of previous sales data, therefore the dreaded 'divide by zero' scenario.

Since the average quantity is only for the previous 90 days, I have created a view that has one record for each client and the average of their previous 90 days of sales.

I also have a view that summarizes each client by month and total quantity.

I'm just not sure how to use this data to only select where the current month's quantity is > 40% of the average.

Hope I made myself clear...

 

[IdoMillet]

It would help if you show the relevant column structure from your VIEWS.
In general, just join the Views according to Cutomer_N, restrict (record selection formula) the monthly rows to last month (a parameter or automatically based on currentdate).

Then, compute the ratio but use an IF THEN logic to first check for zero.
---------------------------
IF {Avg_Last_3} = 0 THEN FALSE
ELSE
{Last Month}/{Avg_Last_3} > 1.4;
---------------------------

Dirty Trick: if you want to avoid the divide by zero problem altogether, use this simplified expression:
--------------------------------
{Last Month} > 1.4 * {Avg_Last_3};
--------------------------------

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[dfaith]

Thank you so much! That's exactly what I needed!