Common Bar/Line Chart scenarios: Example of using the LOOKUP function to align data

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 counts the number of open tickets by technician name. In this example, there is also a blank value in Column A that represents the Unassigned queue.

To ensure the count is correctly aligned to the name of the technician, the LOOKUP function is used. For more information on the LOOKUP function, see LOOKUP.

To build this Klip, you will:

  • Build a Bar and Line chart component,
  • Use the REPLACE function to create the name for the Unassigned queue,
  • Create GROUP and LOOKUP statements for the X and Series sub-components.

This Klip uses the following data source:

Step 1: Building the X-Axis of a Bar/Line Chart component

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select either the use an existing data source from the library or create a new data source option.
  4. From the component tree, select Klip, and type a Klip Title.

Goal: Use the GROUP function to return a unique instance of each technician's name.

Use the REPLACE function to create the name Unassigned that is represented by a blank value in the current data source.

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. This data source does not have a row header.

Location

Action

For our example

From the component tree select X-Axis: Untitled.  
From the data source click the column that contains the names. A:A
With A:A selected click Wrap current value in function, Data Manipulation, GROUP.
This returns a single instance of the name of each technician.
GROUP( A:A )
With A:A selected. click Wrap current value in function, Data Manipulation, REPLACE. GROUP( REPLACE( A:A , matching, with value ) )
With matching selected click Insert function, Data Manipulation, BLANK.  GROUP( REPLACE( A:A , BLANK( count ), with value ))
With with value selected click Insert literal string or number, type Unassigned, and click the Apply button.
This converts the Blank value into the name Unassigned.
GROUP( REPLACE( A:A , BLANK( ), "Unassigned"))
In the formula editing toolbar click GROUP and click the Evaluate Current Selection button to verify the formula is working correctly. Result:
Jack, Jill, Mary, Unassigned
Select the Properties panel. Name the Axis Title and ensure Format as is set to Text. Queue

Step 2: Building the Series of a Bar/Line Chart component

Goal: Use the LOOKUP function to count the number of open tickets by technician name.

Location

Action

For our example

From the component tree select Series: Untitled.  
In the formula editing toolbar click Insert function, Data Manipulation, LOOKUP. LOOKUP( input , keys, results )
With input selected select the REF button and select the reference to the X-Axis you created above.  LOOKUP( Queue, keys, results )
With keys selected copy and paste the formula you created for the X-Axis. LOOKUP( Queue , GROUP( REPLACE( A:A , BLANK( ), "Unassigned" )), results )
With results selected copy and paste the formula you created for the X-Axis and change the GROUP to GROUPBY. LOOKUP( Queue , GROUP( REPLACE( A:A , BLANK( ), "Unassigned" )), GROUPBY (REPLACE (A:A, (BLANK( ), "Unassigned" ), measure, method )
With measure selected click the column containing the date. 
These are the values you want counted.
LOOKUP( Queue , GROUP( REPLACE( A:A , BLANK( ), "Unassigned" )), GROUPBY (REPLACE (A:A, (BLANK( ), "Unassigned" ), B:B , method )
With method selected click Insert literal string or number, type in
count(values) and click the Apply button. 
By default GROUPBY sums the values, in this example a count is needed.
LOOKUP( Queue ,GROUP( REPLACE( A:A , BLANK( ), "Unassigned" )), GROUPBY (REPLACE (A:A, (BLANK( ), "Unassigned" ), B:B , "count(values)" ) )
In the formula editing toolbar click LOOKUP and click Evaluate Current Session to verify the formula is working correctly. Result: 4, 1, 2, 3
Select the Properties panel. Name the Series Label.  
Select the Y Axis sub-component. Name the Axis Title and ensure Format as is set to Number.  
  click Save.  

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