I have a SQL table that I am pulling back 3 variable values from.

The string values for the tables are one of four potential answers "Very Good", "Fair", "Poor", and "Very Poor".

I am switching the value of each question in the first formula.

If they chose "Very Good" I assigning a value of 1 and so forth if isnull then 0(Zero).

 

I need to chart the percentage of each "Very Good" / all answered.

 

In order to chart this out by month I decided to use a switch formula and the chart tool in CRXI-R1.

The first code I run for each variable (e,f,g)

===================================================

ie... Q3e,Q3f,Q3g

 

Switch(

        {table.Q3e} = "Very Poor", 4,
        {table.Q3e} = "Poor", 3,
        {table.Q3e} = "Fair", 2,
        {table.Q3e} = "Very Good", 1,
         isnull({table.Q3e}),0)

===================================================

Then I count the "Very Good" answers with the following code:

 

Ill call this johnny

 

switch( 
 {@Q3e}=1 and {@Q3f}=1 and {@Q3g}=1,1,
 {@Q3e}=0 and {@Q3f}=0 and {@Q3g}=0,0,
 {@Q3e}=0 and {@Q3f}=0 and {@Q3g}=1,0,
 {@Q3e}=0 and {@Q3f}=1 and {@Q3g}=0,0,
 {@Q3e}=0 and {@Q3f}=1 and {@Q3g}=1,0,
 {@Q3e}=1 and {@Q3f}=0 and {@Q3g}=0,0,
 {@Q3e}=1 and {@Q3f}=0 and {@Q3g}=1,0,
 {@Q3e}=1 and {@Q3f}=1 and {@Q3g}=0,0,
 {@Q3e}=0 and {@Q3f}=0 and totext({@Q3g})='',0,
 {@Q3e}=0 and {@Q3f}=1 and totext({@Q3g})='',0,
 {@Q3e}=1 and {@Q3f}=0 and totext({@Q3g})='',0,
 {@Q3e}=1 and {@Q3f}=1 and totext({@Q3g})='',0,
 {@Q3e}=0 and totext({@Q3f})='' and {@Q3g}=0,0,
 {@Q3e}=0 and totext({@Q3f})='' and {@Q3g}=1,0,
 {@Q3e}=0 and totext({@Q3f})='' and totext({@Q3g})='',0,
 {@Q3e}=1 and totext({@Q3f})='' and {@Q3g}=0,0,
 {@Q3e}=1 and totext({@Q3f})='' and {@Q3g}=1,1,
 {@Q3e}=1 and totext({@Q3f})='' and totext({@Q3g})='',0,
  totext({@Q3e})='' and {@Q3f}=0 and {@Q3g}=0,0,
  totext({@Q3e})='' and {@Q3f}=0 and {@Q3g}=1,0,
  totext({@Q3e})='' and {@Q3f}=1 and {@Q3g}=0,0,
  totext({@Q3e})='' and {@Q3f}=1 and {@Q3g}=1,0,
  totext({@Q3e})='' and {@Q3f}=1 and totext({@Q3g})='',0,
  totext({@Q3e})='' and {@Q3f}=0 and totext({@Q3g})='',0,
  totext({@Q3e})='' and totext({@Q3f})='' and {@Q3g}=0,0,
  totext({@Q3e})='' and totext({@Q3f})='' and {@Q3g}=1,0,
  totext({@Q3e})='' and totext({@Q3f})='' and totext({@Q3g})='',0)

====================================================

 

Then to trend it on the chart I use the following

Sum({@Johnny},{@Date},"monthly")/Count({@Johnny}Date},"monthly")

 

the I add it under Data tab for chart under Show Values

I also check Dont Summarize.

 

For January I have 23 answeres where 22 are Very Good and 1 fair I should have a percentage of 95.7 and am only showing 86%

 

Any ideas?