How do I use a past date to specify a future percentage? 1

[elsenorjose]

I didn't really know how to word the subject so here's some more clarification.

I am creating a report which will show me a sales person's conversions for a given date range based on quotes provided in the past. Here's an example.

Sales person A generated 100 quotes in December. I want to see how many of those quotes resulted in sales within 45 days. How would I create a formula and parameter that would allow me to select the month of the quote (December in this example but it must allow for any month) and specify the number of days in the conversion cycle (45 days in this example but should allow for any length of time)?

 

[MJRBIM]

Please post a sample of your data with headers, and an example of how you would like them to be formatted for the report display.

 

[lbass]

I think you could create two parameters,{?QuoteMonth} and{?NumDays}, both as discrete, number parameters. Your record selection formula would be:

month({table.quotedate}) = {?QuoteMonth}

You could add in a clause here that would limit the report to those sales dates that occurred within the conversion cycle, but I'm guessing you might want to also show those quotes that don't result in sales within that time frame. So then you could create a formula {@intime}:

if {table.salesdate} in {table.quotedate} to dateadd("d",
{?NumDays},{table.quotedate}) then 1 else 0

Then you could create a group on salesperson, insert a sum on {@intime} and also a count on {table.quotedate} to compare sales within the conversion period to the number of quotes.

I made a lot of guesses here, and MJRBIM is right that sample data would help--especially re: whether there are separate date fields for sales and quotes or whether there is only one and you have to distinguish them by some other field.

-LB

 

[elsenorjose]

i'm not really sure how to provide the sample data you need so here's a narrative.

we are a mortgage lender. every day we assign applications to our loan consultants and these loans might (all, some, or none) fund. If LC John Doe got 100 loans assigned 45 days ago and 70 actually funded in the 45 days, my conversion ratio is 70%.

My parameter would be the month loan was assigned. So, if I select October, the report should show all loans assigned in October that have funded within 45 days of the assign date and express it as a count and percentage.

Hope this isn't too convoluted.

 

[lbass]

We need to know what your tables are, how they are linked if there are more than one, what the fields are, and the datatype of the fields at a minimum.

Did you try any part of my earlier suggestion? The forum process works best when you respond with the results you get from specific suggestions, so that responses can build upon one another.

-LB

 

[MJRBIM]

OK, from your description - I'm making some data assumptions here.....

NOTE : You will need to paste your own {Table.Field} names into the formulas.

Insert these formulas in your detail -

Name: {@ApplicationDatePlus45}
Formula: {LoanApplications_txt.APPLICATION_DATE} + 45


Name: {@FundedWithin45Count}
Formula: IF ({LoanApplications_txt.FUNDED_DATE} > DateTime (1900,01 ,01 ,00 ,00 ,00 )
AND {LoanApplications_txt.FUNDED_DATE} <= {@ApplicationDatePlus45}) THEN 1 ELSE 0

Group on OFFICER, then insert and Count of the Applications and a sum of {@FundedWithin45Count}, and the following formula in the GroupFooter...

Name: {@FundedWithin45Percentage}
Formula: (Sum ({@FundedWithin45Count}, {LoanApplications_txt.OFFICER})/Count ({LoanApplications_txt.APPLICATION}, {LoanApplications_txt.OFFICER}))*100

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
APPLICATION OFFICER APPLICATION_DATE FUNDED_DATE ApplicationDatePlus45 FundedWithin45Count
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

123465 Bill Jones 10/01/2003 10/24/2003 11/15/2003 1
123473 Bill Jones 10/01/2003 11/15/2003
123457 Bill Jones 10/01/2003 11/15/2003
123471 Bill Jones 10/01/2003 11/15/2003
123469 Bill Jones 10/01/2003 11/15/2003
123459 Bill Jones 10/01/2003 10/24/2003 11/15/2003 1
123467 Bill Jones 10/01/2003 11/15/2003
123461 Bill Jones 10/01/2003 10/24/2003 11/15/2003 1
123463 Bill Jones 10/01/2003 11/15/2003

Application Count 9
Funded Within 45 Count 3
Funded Within 45 Percent 33.33%
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
123458 John Smith 10/01/2003 11/15/2003
123460 John Smith 10/01/2003 11/15/2003
123462 John Smith 10/01/2003 10/24/2003 11/15/2003 1
123456 John Smith 10/01/2003 11/15/2003
123466 John Smith 10/01/2003 10/24/2003 11/15/2003 1
123474 John Smith 10/11/2003 11/25/2003
123468 John Smith 10/01/2003 10/24/2003 11/15/2003 1
123470 John Smith 10/01/2003 11/15/2003
123472 John Smith 10/01/2003 01/13/2004 11/15/2003 0
123464 John Smith 10/01/2003 10/24/2003 11/15/2003 1

Application Count 10
Funded Within 45 Count 4
Funded Within 45 Percent 40.00%
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

[MJRBIM]

Also, those formulas all assume a DateTime field format.

 

[Madawc]

In Crystal 8.5, a slow but reliable method would be to have a detail-line subreport that makes the check for each quote, passing the data of the quote as a parameter. It can pass back the result using Shared Variable, and also say when the order was or if there was no order within the time frame.

Try this if none of the other suggestions work.

Madawc Williams
East Anglia, Great Britain

 

[elsenorjose]

Hey everyone, thanks for the suggestions. I'll be trying them today and post results. Thanks again! This forum is just AWESOME.