Window Calculations (FIRST, LAST, INDEX, LOOKUP)

Window calculations like FIRST(), LAST(), INDEX(), and LOOKUP() operate on specific rows within a partition, enabling relative and positional calculations. They access values at specific positions in the data table. Window calculations are essential for rank-dependent logic, accessing neighboring values, and creating sequential calculations. They compute after data aggregation, and the table structure is determined.

To perform Windows calculations on rows, perform the following steps.

1. Using Kyvos OrderDate, create a view with "Category" on Rows and "Sum Sales" on Columns.

2. Create calculated field: "First Category Sales".

   Open

   

3. Drag First Category Sales to Columns (next to Sales)

4. Right-click the new field pill > "Edit Table Calculation"

5. Set Compute Using to Table (down).

   Open

   

6. Shows the first category's sales value for all rows.

   Open

   

7. Create another field as Sales Rank. The formula is INDEX().

   Open

   

8. Drag Sales Rank to Rows or Columns, Right-click Sales Rank in the view → select Edit Table Calculation.

   Open

   

9. Drag the sum sales to the columns for a better understanding of ranks.

   Open

   

10. Shows the position number of each row in the partition