Cannot Chart Formula Result

[Oglesbay]

I have created a wonderful report that shows the answers to certain questions. The answers are simply labeled as 1 thru 5. What we then want to find out is how many answers are below a 4 and then what percentage that is. We have been able to do this with functions and formulas with ease for each of the 5 questions.

Now, our boss wants us to graph the percentages over the course of the year for the 5 questions. The problem is, the Formula field we created to calculate the percentage does not appear as an available field when looking for the data in the Chart Wizard. Does anyone have any idea how we can get the percentages to appear as an option in the Chart
Wizard? Thanks!

 

[lbass]

You will have to reveal the contents of your formula(s).

-LB

 

[Oglesbay]

What do you mean by "reveal" the formulas? I am not familiar with that?

 

[lbass]

I meant you need to show the content of the formula (and any nested formulas) you are trying to chart on if you would like help. You should also identify your CR version.

-LB

 

[Oglesbay]

I am using CR 11 and here is the formual in the Formula Workshop that will not show up:

(({#Q1CountTotal4}+{#Q1CountTotal5})/{@TotalRecords})*100

Basically it is adding two values then dividing by the total number of records. It is the percentage at the end that we want to chart.

 

[lbass]

You need to share the content of any nested formulas, also (formulas within formulas)--ie., what is in {@Total Records}?

The problem here (in part) is the use of the running totals in a formula. Are you sure you need to be using running totals? How are each of them set up?

-LB

 

[Oglesbay]

As I mentioned at the beginning, the answer of each question is either a 1,2,3,4 or 5. We want to find the percentage of the answers less than 4. Right now the way it is set up (the way I inherited it) is we assign a "1" to each answer. There is a formula for each question like this. We then have 5 running totals that total up the number of each of the 5 questions. What you see in the previous formula is totalling up the number of 4 and 5's and then dividing by another running total that simply counts all the records. As I right this, I think I am starting to see there has to be another way. As I said, I inherited this and was trying to fix their problem the easy way instead of redoing the whole report. Would an IF forumla work better? Any direction would be great.

 

[lbass]

Yes, try using conditional formulas like {@4or5}:

if {table.answer} >= 4 then 1

Then insert a group on the question field and then create a second formula {@4or5percent}:

sum({@4or5},{table.question}) % count({table.question})

Use sum of {@4or5percent} as the summary field in your chart, and {table.question} as the "on change of" field.

-LB

 

[Oglesbay]

It worked perfectly. Thank you so much. Now the one thing is though, that allowed me to chart one month of data. I can guaruntee you though that my boss is now going to want to chart a whole year. So how can I have it do this for each month and return a percentage for each month. Would I use an If statement to say if it is within the month of January, then count? And create one for each month?

 

[lbass]

I would insert an outer group on date (on change of month) with question being the inner group, and place the chart in the group footer for month. You would have to change your conditional formulas to add a group condition to the total, as in:

sum({@4or5},{table.question}) % count({table.question},{table.date},"Monthly")

-LB

 

[Oglesbay]

I have been playing with this for the last couple hours and no matter what I try I keep getting errors, the one I get most often is "The special variable 'Formula' must be assigned a value within the formula."

What I have set up is the forumla:
if {tbl2004WebEvals.CourseActivities}>=4 then 1

Then I created another formula that is in the group footer:
sum({@Question 1 - 4 or 5})% Count ({tbl2004WebEvals.CourseObjectives}, {tbl2004WebEvals.Date}, "monthly")

I also have it grouped by the date category and to group by change in month. I just can't get all of this to work. Any other ideas?

 

[lbass]

Go into the formulas and make sure they are set to Crystal Syntax, not basic syntax.

-LB

 

[Oglesbay]

Ok, fixed it but I got an answer of 301.44 and when I switched the two variables around I got .33? The answer should be 96.7% if our manual calculations are correct.

 

[lbass]

What two variables?

In the last post where you showed your percentage formula, you forgot to include the {table.question} group. Should have been:

sum({@Question 1 - 4 or 5},{table.question})% Count ({tbl2004WebEvals.CourseObjectives}, {tbl2004WebEvals.Date}, "monthly")

-LB

 

[Oglesbay]

When I add the group I get an error that says "There must be a group that matches this field." Any ideas on that one?

 

[lbass]

You need to have a group #1 on {tbl2004WebEvals.Date} on change of month, and a group #2 on {table.question} (substituting your actual field name for question, of course).

-LB