Line Chart Based On Compliance Formula?

[MarkEdmondson]

Good evening all,

I'm a relatively inexperienced CR11 user (been tinkering for about a year).

I've created a number of reports which measure compliance of several measures based off of Y/N entries in the db. I've created Running Totals on those to count up the Ys and Ns. From there I've created a compliance formula of Y Count/(Y Count+NCount)*100 basically.

What I'd like to do from there is create a simple line graph trending that compliance percentage over time. When using the Graph Wizard, that particular formula is not available as a source option.

Can anyone point me in a the right direction (if it's even possible)?

Thank You

 

[Madawc]

I don't use graphs much, but since no one else has offered an answer, I'll try.

I think you need to make it a summary total, or a formula based on summary totals rather than running totals. Try creating a formula field at detail line level, something like

if {your.field} = "Y" then 1 else 0

You can do a summary total of this field and should be able to graph it. Maybe also a formula based on it.


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

 

[MarkEdmondson]

It's gonna take some machinations, but that's going to work well, thanks!

 

[MarkEdmondson]

I've applied the approach above, and it's working quite well, but there's a wrinkle. The field I'm studying compliance on has three possible answers....Y, N and N/A. If I use the Summary "Average" option, I get the result I want if only Y and N are in field, but if there's an N/A there, it's added to the denominator and throws off the percentage (when it should be ignored).

Is there a line I can add to the code posted above that would give me:

Y=1
N=0
N/A=null

Thanks?

 

[lbass]

You could try the following. Create a formula {@null} by opening and saving a new formula without entering anything. Then change the formula to:

if isnull({yourfield}) then
tonumber({@null}) else
if {your.field} = "Y" then
1 else 0

-LB

 

[MarkEdmondson]

Thanks to all of those that helped with the above. I've begun being able to chart these measures, and it's been a great help. I used lbass's formula to assign a numeric value to the question, and then the summary average feature to measure compliance.

I now have one further question. In a couple of areas our compliance summary is based on the "average" compliance of as many as 7 Y/N/NA questions.

These 7 questions would all be a part of a single line item. My bosses want only the total compliance charted. On my dashboard I've written formulas to add up all the Ys and add up all the Ns, and calculate compliance off of that. That of course, just like above, is not chartable. The system provided here though is also not chartable for me either, as it seemingly can only be applied to a single question.

I don't know if this makes any sense. Any ideas?

 

[lbass]

What is the content of the formulas that you would like to chart on?

-LB

 

[MarkEdmondson]

OK, let me see if I can clearly explain.

I've got a database with survey questions, each question can be answered Y/N or NA. Using your formula above (thank you again) I've given them a numeric value in the details line.

I've then used the Summary tool and averaged my Y/N calculation field and that's resulted in a compliance percentage that can be placed in graph.

I'd like to be able to do the exact same thing, but with 3 seperate questions summarized into one compliance number.

 

[lbass]

Is the average of all detail level records or of the averages per question? They could be different. If it is the average of all detail records for those three questions, you can create an outer group that contains those three questions versus all others, and then insert the average per that group.

-LB