Complex what if analysis on text fields

[phcaptjim]

I have a question about what if analysis on text based fields.  I've looked at numerous examples using number fields, and that all seems fairly straight forward, however in my case it's going to be a bit more challenging.

Here is what my data set looks like:

PROV_ID	MEASURE_1	MEASURE_2	MEASURE_3	MEASURE_4	COMPLETE_FLAG
AA	Y	Y	Y	Y	Y
BB	N	N	Y	Y	N
CC	N	Y	N	N	N
DD	N	Y	Y	Y	N

I would like to do analysis that says, what if Measure 1 was 'Y' for all providers, how many would be complete?  OR what is Measure 2 is 'Y' how many providers would be complete?  One of the challenging issues is the COMPLETE_FLAG is set in the data, so by just using a variable for one of the measures, that is not going to charge the complete flag.  I'm  guessing I need to calculate that flag on the fly depending on the variable value for each measure.

Any help would be appreciated!

[swuehl]

Not sure if I understand completely. Shouldn't COMPLETE_FLAG for PROV_ID DD be 'N'?

If you assume Measure_1 complete for all providers, you need to check the other measures only and then add up if all measures are 'Y', right?

maybe like

=sum(-1 * (MEASURE_2='Y') * (MEASURE_3='Y') * (MEASURE_4='Y') )

[phcaptjim]

PROV_ID DD should be 'N', sorry about that.

Your logic seems to make sense, let me try that in practice and see what happens.  This might be tricky in that I have about 40 measures to look at in my actual data model, I trimmed down my example to make it more practical for this forum.

[swuehl]

Another idea would be to prepare a table in your script, that holds all providers with exactely one measure being 'N' and the measure name that is 'N'.

Then, when you want to query 'what if measure1 is Y for all providers', just add up the providers in that table with 'measure1' set and add the count of COMPLETE_FLAG = 'Y'.

edit: attached a sample