How to pull a value when a field equals a value - Crystal 8.5

[envirodata]

In Crystal I'm struggling with how to design a report that will pull from a field when the field is a particular value. More specifically, I'm trying to find the percentage of gifts that came in from the no. of people solicited. The problem is that a person could have received up to 5 solicitations.

For example, I have a list of gifts made in June and each gift is assigned an appeal and a package. The appeal for each of these gifts is June 2008 and the package will be 1st, 2nd, 3rd, 4th, or 5th based on which appeal they responded to. I also have no. solicited next to each package. What I want is the number of total gifts that came in as a percentage of just the 1st notice (which is a true count of people not letters sent). So, how do I find this percentage of gifts received based on no. solicited in package 1st notice but exclude (or even just hide the results) of other notices.

Let me type some example data rows to make this clearer:

Gift Date Appeal ID Package ID Package No. Solicited
6/1/08 R0608 1st Package 10
6/1/08 R0608 1st Package 10
6/1/08 R0608 2nd Package 5
6/1/08 R0608 3rd Package 4

So, I would total the number of gifts. Then I would like to find the response rate as total number of gifts/1st package.

So, in this example, it would be 4/10 or 40%. And to complicate things more, I would actually like to take the no. solicited from 2 packages on some months(so, the total of no. solicted in package 1A and in package 1B but exclude other packages).

Thank you for any guidance you may have in setting this up.

 

[lbass]

Is "Package No. Solicited" an actual database field or is it a summary? Does your "package ID" field actually say "1st Package"? In the last scenario, where you might want to test for more than one package, what does the package ID field actually look like?

-LB

 

[Madawc]

I think you need a running total, using a test. Then compare this to the count of items.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

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

 

[envirodata]

'Package number solicited' is an actual database field. The package ID field is named '1st notice' and I would like to to add the no. solicited from this field to the package ID field '50+ notice' and divide that number by the total count of gifts (but the count of gifts should include all packages with that appeal id - not just the 1st and 50+).

Thank you, LB!

 

[envirodata]

Sorry, I just made a mistake - the Field name is 'Package ID' and the values are 1st notice or 50+ notice (or 2nd notice, 3rd notice, etc.). They are not fields themselves but the values of the fields in the database. But 'Package number solicited' is a database field.

 

[lbass]

First insert a group on {table.AppealID}, and then create a formula:

//{@1stor50}:
if {table.packageID} in ["1st Package","50+ Notice"] then 1

Then create a second formula:

sum({@1stor50},{table.appealID})%count({table.appealID},{table.appealID})

Place this on the report in the appeal ID group footer or header and then click on the % icon in the toolbar.

-LB

 

[envirodata]

Thank you, LB. I am still having a problem with this. I tried to insert the formula above and it said that the field name is not known. Then I tried using this //{@1stor50}:
if {table.GfApls_1ApApP_1_Package} in ["1st Package","50+ Notice"] then 1

Because this looks like the name of the field in Crystal but it still says that the field name is not known.

 

[lbass]

When creating the formula, doubleclick on the field in your field list to automatically get the correct {table.field} name. I just use "table" as a generic term to indicate where your table name should go.

-LB

 

[envirodata]

Thank you, LB - and sorry it has taken so long to get back to this. I successfully created the first formula above but when I try to create the second formula, the cursor goes back to {@1stor50} and says that the field name is not known.

 

[lbass]

You have to name your first formula "1stor50" (without the quotes).

-LB

 

[envirodata]

I do think you are genius - this is much further than I could ever get on my own and that you're not looking at the data is even more impressive.

I am again, however, stuck. After following the instructions, I'm coming up with a retention rate of 28.57% and it should be 60.13%.

I do know what the problem is but I don't know how to fix it. The formula is taking the 1st notice and 50+ notice gifts count divided by the total count of gifts. What I actually need to find the retention rate is the no. solicited (50+ and 1st notice together) divided by the total count of gifts.

The number solicited is a database field but where I see a problem is that it will have a value for every gift. Meaning, every 50+ gift will have a no. solicited attached to it - and I only want to pull on this number one time and I only want to pull on the 1st notice no. solicited one time.

As in this example:

Gift Date Appeal ID Package ID Package No. Solicited
6/1/08 R0608 1st Package 10
6/1/08 R0608 1st Package 10
6/1/08 R0608 2nd Package 5
6/1/08 R0608 3rd Package 4
6/1/08 R0608 50+ Package 2
6/1/08 R0608 50+ Package 2

I want to find the total count of gifts (6 in this example) and divide by the sum of no. solicited in package 50+ and 1st package (10+2 or 12 in this example) - to get a retention rate of 50%. But each gift is assigned the package no. solicited and I only want to pull on the no. solicited 1st and 50+ and only one time - not for every gift.

Is this possible?

Thank you!

 

[lbass]

Use a running total {#1stor50} instead of the conditional formula where you sum the No_Solicited, evaluate using a formula:

(
onfirstrecord or
{table.appealID} <> previous({table.appealID}) or
{table.packageID} <> previous({table.packageID})
) and
{table.packageID} in ["1st Package","50+ Notice"]

Reset on change of group: appeal ID. This assumes you have sorted by package ID.

Then create a formula for the Group Footer (has to be here):

count({table.appealID},{table.appealID})% {#1stor50}

-LB

 

[envirodata]

It tells me that #1stor50 field name is not known - and I did name the first formula this.

Also, what do you mean by Reset on change of group: appeal ID?

Thank you.

 

[lbass]

You should not be using the original formula any more. Instead you are inserting a running total that you are nameing "1stor50". Go to the field explorer->running total and then follow the earlier steps. Reset is the third part of the running total.

-LB

 

[envirodata]

Oh, I see. I've almost got it - just one more question - what is the type of sum on the no. solicited field - choices being maximum, minimum, count, distinct count, etc.? Thank you.

 

[lbass]

The summary IS a sum.

-LB

 

[envirodata]

Sum is not on the drop-down menu.

 

[envirodata]

To be clear, the type of summary drop-down list includes:

maximum
minimum
count
distinct count
Nth largest
Nth smallest
mode
Nth most frequent

Thank you for your help.

 

[lbass]

Surely No_Solicited is a number datatype, isn't it? Then sum would be available. If it isn't, convert it by using a formula:

tonumber({table.No_solicited})

Then sum this formula in the running total.

-LB

 

[envirodata]

Okay, I have followed the instructions closely and double-checked but am coming up with only 1.53% when it should be just over 60%.

Above, when you say, Reset on change of group: appeal id. This assumes you have sorted by package id. Do you mean that taking the step to reset on change of group: appeal id is sufficient or do I need to take an additional step to ensure that the field is sorted by package id?

Please let me know if you have insight as to where I could have gone wrong. Thank you!