Complex Calcuation

[cr-dev]

Hello all,

I am working on a calculation that will present a percentage in a field based on a radio buttons 1 thru 5 and NA selection. (1 being excellent, 5 being poor) NA would just be a "0".

There are 4 sections, each section containing anywhere from 3 to 6 questions each.

What I have to determine is the percentage of satisfaction in a field. So I am able to break down the questions worth (in percentage) as such:

1 = 100

2 = 75

3 = 50

4 = 25

5 = 0

NA = 0

So for example, I have 3 questions and the person rates a "1", a "2" and a "2". So 250 / 3 would be 83.3 percent.

I am trying to get the percentage field for that section (each has its own) to evaluate and populate with the correct percent.

How would I create the calculation for the total percent field to transpose the 1,2,3,etc into 100,75,50 and the like? Once all the numbers are done, it will be easy to perform the assessment of the totals.

Thanks for your help in advance!

Chris

[Fitch]

Let( NA=5 ;

    Choose(satisfaction; ""; 100; 75; 50; 25; 0 )

     )

[comment]

Try:

Case ( Selection ; ( 5 - Selection ) * 25 ; 0 )

Though if you want a real percentage, it should be:

Case ( Selection ; ( 5 - Selection ) * 0.25 ; 0 )

Let( NA=5 ;

Does that part do anything?

[comment]

Or, even simpler:

Mod ( 5 - Selection ; 5 ) * 25

[cr-dev]

Thank you Fitch and Comment! I got pulled off on another fire the past couple of days. I didn't get to test your example yet but will be doing it this afternoon.

This particular database is quite a challenge. The reporting the management wants from this system is very granular.

Could I use the same calculation to get a percentage total based on the "found records" in a report and post to a field that is only setup to post that number? This would not include found records that has a value of "NA" (people who were in the system but did not do a survey).

[comment]

What is a "percentage total"?

[Fitch]

When you want to work with totals for a found set of records, you're generally talking about summary fields.

If you want to perform calculations with a summary field you can use the GetSummary function.

[cr-dev]

Percentage Total is from the overall total for each survey (overall total determine from 4 areas of evaluation added up and / 4)that was found in a given date range (ie: 1/1/08...3/31/08) and those "overall totals" added up and divided by the "found count" of records to provide what I have defined as a "Percentage Total".

Example:

Sally Smith

Scheduling: 84.5 Education: 100 Accommodations: 100 Customer Service: 58.7 343.2 / 4 = 85.8%

For a quarterly report, I might find 67 evals and so I would take those overall totals and divide by the found count of records (67) to get my overall customer satisfaction for that quarter. Equally I can get the same for bi-annual and annual if the end user desires.

As I have developed this system and got more and more involved with the information, this system is without question, the most difficult reporting I have found to date. Out of a possible 26 questions, there are 156 different responses to choose from.

Fitch, I was playing with summary fields option but had a time getting the system to evaluate with this field type using a calculation. Said something about, "cannot reference this field due to a type mis-match" or something. That was on Friday. I'll work with it somemore today and tomorrow.

Edited June 23, 2008 by Guest

[comment]

What you describe is an average. However, it's not clear if you are averaging base data or summary values. A better description of your structure would be helpful. I am getting the impression you are using multiple fields where you should be using multiple records. See also:

http://fmforums.com/forum/showtopic.php?tid/180113/

http://fmforums.com/forum/showtopic.php?tid/183639/

http://fmforums.com/forum/showtopic.php?tid/190074/

http://fmforums.com/forum/showtopic.php?tid/192706/