Common Table scenarios: Sorting data to display the video with the highest number of views

Some formulas are used more often than others when building Klips. In this series of articles, a Klip is built using a common set of formulas to give a better understanding of how these formulas work within a sample data set.

This Klip returns the highest ranked video in the last three days. Although this example uses a simple formula, this concept can be used with other formulas.

This Klip uses the SORT function. The SORT function allows you to sort by a column within a table. The SORT function must be applied to all columns in the table.

To build this Klip, you will:

  • Build a Table component
  • Use the SORT function to rearrange the data

This Klip uses the following data source:

Step 1: Building a Table component

Goal: Use the SORT function to sort the columns of data by the "Views" column, ie column C.

Note: If the column has a row header in your data source, wrap each column reference in the SLICE function. By default, the SLICE function removes the column or row header. For more information, see SLICE function.

  1. Select Build a New Klip.
  2. Select the Table component.
  3. Select Use an existing data source from the library and select your data source.
  4. From the component tree, select Klip, and type a Klip Title.

Location

Action

For our example

From the component tree select Column: Unnamed 1.  
In the formula editing toolbar select the column that contains the date - Column A. A:A
With column A:A selected select Wrap current value in function, Data Manipulation, SLICE.
If your data source does not have a row header, you can skip this step.
SLICE( A:A , start , end )
With SLICE selected select Wrap current value in function, Data Manipulation, SORT.
SORT( SLICE( A:A , start , end ) , type , return values )
With SLICE selected using Ctrl+C and Ctrl+V copy and paste the SLICE( A:A part of the formula to return values.
The formula copied to return values is what you want to display, for this column, in the table.
SORT( SLICE( A:A ) , type , SLICE( A:A , start , end)
With the first A:A selected select the column that contains the number of views - Column C.
The first part of the SORT formula contains the formula of the column that you want the data to be sorted by.
SORT( SLICE( C:C ) , type , SLICE( A:A )
With type selected select descending numeric.
This sorts the column by the highest number of views to the lowest number of views.
Numeric because column C is formatted as a number.
SORT( SLICE( C:C ) , descending numeric, SLICE( A:A )
With SORT selected using Ctrl+C and Ctrl+V, copy and paste  the SORT formula to the Column: Unnamed 2 and Column: Unnamed 3 in the table and change reference A:A to B:B for Unnamed2 and C:C for Unnamed3.
SORT( SLICE( C:C ) , descending numeric, SLICE( B:B )

SORT( SLICE( C:C ) , descending numeric, SLICE( C:C )
With the Table sub-component selected select the Properties panel and at Table Rows, select Show at most 1 Rows.
This limits the display of data to the video with the highest number of views.
 
With Column: Unnamed 1 selected, select the Properties panel

Type a Column Header for each column and select the following format types.

Remove Column: Unnamed 4 using the - sign.

Column: Unnamed 1: Date - Date and Time - Input Format Custom d-MMM-yy and Output Format Custom d-MMM-yy

Column: Unnamed 2: Video - Text

Column: Unnamed 3 : Views - Number
  save the Klip.  

Klip Tutorials

  1. Introduction
  2. Introduction to the Build-a-Klip Tutorial
  3. Build-a-Klip Tutorial: Phase One
  4. Build-a-Klip Tutorial: Phase Two
  5. Build-a-Klip Tutorial: Phase Three
  6. Build-a-Klip Tutorial: Phase Four
  7. Build-a-Klip Tutorial: Phase Five
  8. Example - Revenue YTD Klip
  9. Example - Financial Performance YTD Klip
  10. Example - World News Klip
  11. Example - Bookings by Country Klip
  12. Example - Bookings QTD Klip
  13. Example - Stock Quotes Klip
  14. Example - Marketing Traffic Sources Klip
  15. Example - 75/20 Service Level - Today Klip
  16. Training Klip - Array, Slice, Reverse, If, and Trim Functions
  17. Training Klip - Sum, Average, and Count Functions
  18. Training Klip - Group and Groupby Functions
  19. Training Klip - Date, Datevalue, and Today Functions
  20. Training Klip - Replace function and image URLs
  21. Training Klip - Concat and Join Functions
  22. Common Bar/Line Chart scenarios: Example of using the LOOKUP function with a condition to align data
  23. Common Bar/Line Chart scenarios: Example of using the LOOKUP function to align data
  24. Common User Input Control scenarios: Displaying total boxes of fruit sold in the last three months
  25. Common User Input Control scenarios: Counting all tickets by quarter, by month, by weekday
  26. Common Table scenarios: Selecting start and end dates to display number of boxes sold by country
  27. Common Table scenarios: Sorting data to display the video with the highest number of views
  28. Common Pie Chart scenarios: Displaying total number of surveys as a percentage by country
  29. Common Pie Chart scenarios: Displaying numbers and percentages in a pie chart legend

Feedback and Knowledge Base