Case Study: Converting Decimals to Percentages

This case study demonstrates how to convert decimals to percentages in Lambda Analytics.

Introduction to Converting Numeric Measures

Grades and other numeric measures routinely contain values with multiple decimal points. Often when creating reports and dashboards for presentations, certain forms of data (grades, for example) are best suited to be displayed as percentages instead of extended decimal strings.

Using the New Calculated Measure window in the Ad Hoc Editor, you can create a calculated measure to convert decimal strings to percentages. For the purposes of this case study, we will walk through converting the mdl_grades_grades.finalgrades field from decimals to percentages.

Converting Grades to Percentages

NOTE  NOTE

This walkthrough assumes that you have opened a new Ad Hoc View in the Ad Hoc Editor from the Grades domain.

1.  In the Measures panel of the Ad Hoc editor, hover over the title bar menu and select Create Calculated Measure.

2.  In the Measure Name field, enter a name for the new calculated measure.

3.  In the Functions list on the Formula Builder tab, select the Concatenate function. The concatenation formula will appear in the Formula box.

4.  Select the TextFieldName portion of the formula.

5.  From the Functions list, find the Round function and double-click it to replace the selected text with the formula.

6.  Select the NumberFieldName portion of the formula.

7.  From the Functions list, find the IF function and double-click it to replace the selected text with the formula.

8.  Select the BooleanFieldName portion of the formula. 

9.  From the Functions list, find the IsNull function and double-click it to replace the selected text with the formula. 

10.  Select the FieldName portion of the formula.

11.  From the Fields and Measures list, find the Final Grade measure and double-click it to replace the selected text within the formula. 

12.  Replace the TrueCalc portion of the formula with the integer "0" and replace the FalseCalc portion of the formula with the Final Grade measure.

13.  Replace the Integer portion of the formula with the integer "0" and the Text String portion of the formula with the % symbol. 

14.  Finally, delete the commo and ellipses from the end of the formula. Your completed formula should appear as follows:

Concatenate(Round(IF(IsNull("Final Grade"), 0, "Final Grade"), 0), '%')

15.  To ensure that your formula returns a correct value, click Validate.

16.  To save the calculated measure, click Create Measure. The new 'Final Grades' calculated  now appears in the measures panel, and can be added to your view in the same manner as other measures. 

The following table breaks down the full formula into the four functions and offers an explanation of each step of the conversion:

Function 4 Function 3 Function 2 Function 1 Function 2 Function 3 Function 4
Concatenate( Round( IF( IsNull("Final Grade") , 0, "Final Grade") , 0) , '%'
Function Explanation Case 1 Case 2
Function 1 If the value of "Final Grade" is null, this function returns the value "TRUE"
If the value of "Final Grade" is not null, return is "False"
If "Final Grade" = "92.312" 
Return is FALSE 
If "Final Grade" = "null"
Return is TRUE 
Function 2 If the return value of Function 1 is "TRUE," this function returns "0"
If Function 1 is "FALSE," this function returns the "Final Grade" integer
If function 1 = "FALSE"
Return is "92.312
If function1 = "TRUE"
Return is "0
Function 3 This function rounds the return of function 2 to the identified number of decimal points - in this case: zero If function 2 = "92.312"
Return is "92"
If function 2 = "0"
Return is "0"
Function 4 This function concatenates the return of function 3 with the identified string - in this case: the % symbol If function 3 = "92"
Return is "92%"
If function 3 = "0"
Return is "0%