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
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%"