This case study demonstrates how to use the rank function to create row numbers in Lambda Analytics.
Introduction to the Rank Function
When used as a formula for a calculated measure, the Rank function assigns each row a unique numeric value that represents a ranking of a corresponding field value. If the corresponding field values are all unique, the row numbers will be unique.
For example, the function Rank("student_id") would assign a value to each student ID in a corresponding (and separate) column, with the (numerically) lowest student ID value corresponding to 1.
For the purposes of this case study, we will use the Course Completion Table View, creating Row Numbers based on the Course Completions ID (coursecompletions.coursecompletions_id).
Adding Row Numbers to the Table View
To create row numbers for the Course Completion table view:
1. In the Repository, navigate to Public > Moodle/Totara > Ad Hoc Components > Views.
2. Select the Course Completion Table View.
3. In the Measures panel, hover over the Detail Selector icon to expand the Details menu.
4. Select Create Calculated Measure.
5. From the Functions list, double-click Rank to enter it into Formula box.
6. In the Formula box, select the NumberFieldName so that it remains highlighted.
7. From the Fields and Functions list, select the Course Completions ID. Double-click the Course Completions ID so that it replaces the selected text in the Formula box.
Your formula (read as: Rank("Course Completions ID")) will create a calculated measure that assign each Course Completion ID a ranking value.
8. Click Validate to ensure there are no errors in your formula.
9. In the Measure Name field, name your new measure "Row Number."
10. Click the Create Measure button. Your new Row Number measure will appear in the Measures Panel.
Adding Sorting Orders to Row Numbers
To add and assign a sorting order for your row numbers:
1. From the Measures panel, select the Row Number calculated measure and drag it into the columns box.
The Row Number column appears at the end of your table. Note that each row now has a unique number, corresponding to the unique Course Completion ID.
2. To sort the values, right-click the Row Number column and select Use for Sorting.
3. In the Sort On panel, click the Row Number value to alternate between the two available sorting arrangements: ascending or descending.
4. Click OK to save your changes. Your row numbers will now be sorted.