Computing KPIs from CSV data

A KPI or a Key Performance Indicator is a quantity that indicates the performance of an activity. Examples include:

  • number of countries that have harnessed wind energy for electric power generation.
  • percentage of children enrolled in secondary education.

KPIs represent a summarization of detailed data which provides a good indicator of a measurable quantity. They can be computed from CSV data as we will show below.

Importing CSV data into the grid editor

Counting unique values in a text field

The Analytics section allows you to explore various aspects of your CSV file including:

  • Unique values in text fields.
  • Statistical information about numeric fields (average, stddev, etc.)
  • Grouping (or aggregates) fields and computing different types of metrics on those groups.

Let us now see how we can view the unique values in a text field.

  • In the Analytics tab, ensure that the Metrics pill is active (1).
  • Choose the column for which you want to display the unique values (2).
  • The type of the column is shown (3). The type is automatically identified from the first few rows when opening the CSV.
  • The unique values along with the occurence counts are displayed in the table below (4).

Viewing statistics for a numeric field

  • When you have a number field in your CSV, you can view the statistics for that field quite easily. To begin with, ensure that the Metrics tab is selected (1).
  • Choose a numeric column from the columns dropdown (2).
  • The type of the column is indicated as shown (3).
  • The statistics for the column is shown (4).

Computing a key performance indicator (KPI)

  • Now that we know how to obtain statistics from CSV data, let us compute a key performance indicator (KPI) we need. First, we need to find the total power generated in the world by wind energy during 2017.
  • First switch to the Grid editor tab (1).
  • Click the search icon to perform a search (2). The search panel appears above the grid.
  • Ensure that the Javascript function tab is selected (3).
  • Enter the javascript expression to select rows with Year equal to 2017 (4).
    row => row.Year == 2017
  • Click the search icon inside the panel. The search is executed and the grid is updated (5).
  • The number of rows matching the query is also shown (6).
  • After this is done, we click on the Analytics tab to get back to the analytics panel.
  • In the analytics panel, ensure that the Metrics pill is selected and choose the Quantity column from the dropdown. Note down the value of the field sum in the displayed statistics (currently 1113817.36).

    This is the total amount of electricity produced by wind energy in 2017.

  • Repeat this process with another CSV file containing data about total electricity production from all sources for each country.

    From this, we determine that the total electricity generated in the world in 2017 was 25599352.07.

Summary

  • Computing key performance indicators may involve cleaning up the data and manipulating it to get it to the right form. This can be performed using the grid editor.
  • Once the data has been cleaned up, you can use the analytics section of the software to summarize information in the CSV file.

See Also