# 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.

### 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.

### 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.