Any Ideas on How to do This? 1

[butkus]

I have a database that tracks client survey responses in three month increments. The first entry for any client is intake, followed by a 3 month/6 month/9 month ect... eval.

What I would like to do, is identify (and limit the report to) those clients who have been in the program for at least 18 months (or greater) and report their responses (graphically) for their 3/6/9/12/15/18 surveys - and so on.

The problem I see is we could have 300 clients who completed the 3 and 6 month survey, but of them only 100 might have stuck around to make it to 18 months. Its the 100 I'm interested in because of their longevity in the program.

Any ideas are always appreciated.

 

[kenhamady]

It is always more complicated when you have to evaluate two different records to implement criteria. You have to compare the first with the last, which means CR has to pull all records into memory.

Any chance that you have a master record for each person that has their initial date (stored with their demographics)? If so this will be much simpler, because when you link these records to the surveys, you have both dates in one record. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[butkus]

Ken,

No sir, its a flat table. But what you said made me think of something. Can I bring the table to the report twice (an alias table)and limit one of them to intake data and the other to 18 months or greater (eliminating those who did not stick around)?

 

[kenhamady]

Possibly.

But only if you can identify the Intake record without referring to any other records. In other words, if I showed you just one record, could you tell that it was an intake record or not an intake record? If you have intake records marked, then you can alias the table as a master table.

If you can only tell by the fact that it is the first of several, then the alias won't help you simplify things much. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[pelajhia]

I just read this post and thought it might help, if you just need to view the records where the count of the client id > 2.

thread767-459557

 

[butkus]

Ken,

I think I can distinguish intake records from subsequent records. The table is structured to allow for the survey period (3/6/9/etc...) to be entered. On the intake record, the field is left blank - I can either leave it blank or convert it to some descriptive flag (like 0 or INTAKE)(Its a string field(don't ask why)).

Assuming this modification, do you think the alias table idea would provide the base for this report?

 

[kenhamady]

Yes, you could use an alias of this table as your demographics table (By selecting only the intake records from this table). Then link this to the surveys table (itself) by Consumer.

Only select records where the gap between intake date (alies) and survey date (full table) is greater than your minimum. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[butkus]

Ken, thanks for the info thus far.

The only remaining question I have is how to do what you last said. I certainly know how to limit the alias table to just intake records, but how do I then

"Only select records where the gap between intake date (alies) and survey date (full table) is greater than your minimum"

do this. Can it be accomplished in the select expert.

Sorry, it's been a few days since I last looked at this.

 

[kenhamady]

First write a formula that calculates the gap. This would be:

{FullTable.Date} - {AliasTable.Date}

Then you can use your select expert to add a rule that says:

{@Formula} > is greater than or equal to > 180 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[HowardHammerman]

Here is another approach.

Group on client ID. Create a count at the group level.
Since you do the eval every three months, you want those groups with a count of 6 or more. So right-click on the group count and enter >= 6.

You are now down to the 100 people who were in the program 18 month.

Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[butkus]

Howard, I think I see where you are going, but I don't fully understand. I created the count at he group level, and then you mention "right-click on the group count and enter >= 6" What exactly does that mean? Should that be used in a supression formula.

 

[butkus]

I fugured out what you wanted (that should be in the select expert - right) - works like a champ.

 

[kenhamady]

Using a subtotal will shorten the report, but the charts you mentioned in the first post will still include all groups, even those whose subtotal doesn't qualify. The alias technique uses record selection, which allows your charts to reflect only those groups that meet your criteria. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[butkus]

Ken,

I looked into what you said, and it appears as though the report is in fact looking at all client id's - as you say (I can see all client names listed on the left hand side of the report-the group). But (and I'm quessing here) by limiting the report to the groups meeting the criteria, my totals appears to be working for just the names returned to the output.

My selection criteria follows:
{@period value} <= 18.00 and
Maximum ({@period value}, {tblintake.newid}) = 18.00

I approached it a little differently than Howard suggested (because not all clients have completed all survey's (ie some have intake/3/6/15/18 or intake/18))

Anyway, I did some hand counts and it appears to be working. Do you believe that I have missed anything?

 

[kenhamady]

If you use running totals, you will get only those meeting the &quot;Max&quot; criteria. If you use regular totals, you will get all those listed in the group tree on the left. It is tricky to do something 'graphical' with running totals. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[butkus]

I am using running totals, but I had to create 7 of them for each demographic. Example:
Maintains Weight-Intake
Maintains Weight-3
Maintains Weight-6
and so on to 18.

I use a formula in the running total

{@period value}=0.00 and
{tblintake.weight}=&quot;Yes&quot; --->Intake

{@period value}=3.00 and
{tblintake.weight}=&quot;Yes&quot; --->3 month

and so on.

The totals appear to be working fine.

Thanks to Ken and Howard for the helpful ideas.