Analyse: Creating a calculated field

Modified on Tue, 24 Mar at 1:04 AM

In the attached video and example we create a field and Measure to calculate a Grade Point Average.

To perform any mathematical operations, we need to ensure any data that is used for the calculation is numerical.

This is first achieved by converting any field, that contains a GRADE or TEXT, to a number.

The video attached will assist in this explanation.

The fundamental steps are:

Identify the field/s that are collecting the same data to be calculated for an average.

If the data is TEXT then a new field needs to be created that converts the TEXT, to a Number.

Examples are:

5-Point Scale: 'A'=5, 'B'=4 ... 'E'=1 etc.
5 Point Scale from a 15 Point Scale: 'A+'=5, 'A'=5, 'A-'=5, 'B+'=4, B=4, 'B-'=4,... 'E-'=1
3 Point Scale: 'H'=3,'S'=2,'U'=1

This can be achieved by using a formulae such as:

CaseWhen("Affective Value 01" == 'A', 5, "Affective Value 01" == 'B', 4, "Affective Value 01" == 'C', 3, "Affective Value 01" == 'D', 2, "Affective Value 01" == 'E', 1, 0)

Once a field is created that captures the values, you may create a NEW MEASURE that will then SUM the fields to be averaged.

The Formula in the example for a GPA Measure is:

("Field 1" + "Field 2" + "Field 3" + "Field 4") / Number of Fields(4)

You need to set the Summary Calculation, of this field, to Average.