# Common Bar/Line Chart scenarios: Example of using the LOOKUP function with a condition 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 yes and no records by date.  It uses a separate series to create a condition for either yes or no. To ensure the count of records remains aligned to the date, 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,
• Create GROUP and LOOKUP statements for the X-Axis and the 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 give your Klip a Title.

Goal: Use the GROUP function to return one unique instance of the date.

Note: If the column has a row header in your data source, wrap each column reference in the SLICE function. For more information, see SLICE function.  By default, the SLICE function removes the column or row header. If your data source does not have a row header, you can omit this step in our example.

### For our example

From the component tree select X-Axis: Untitled.
From the data source click the column that contains the date. `A:A`
With column A:A selected click Wrap current value in function, Data Manipulation, SLICE, to remove the row header. `SLICE( A:A , start , end )`
With SLICE( A:A selected click Wrap current value in function, Data Manipulation, GROUP. This returns a single occurrence of each date. `GROUP( SLICE( A:A , start , end )`
In the formula editing toolbar click GROUP and then click the Evaluate Current Selection button to verify the formula is working correctly. Click the Finished button. Result:
Aug-13, Dec-13, Jul-13, Jun-13, Nov-13, Oct-13, Sep-13
Select the Properties panel. Set the X-Axis properties.  Name the Axis Title, change Format as to Date and Time, Output Format, Custom MMM/yyyy, Label Angle to sideways, and Sort Order to Ascending.

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

Goal: Use the LOOKUP function to count the number of yes and no records by date by creating two different series.

### 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( Date , keys, results )
With keys selected copy and paste the formula you created for the X-Axis. To do this, use Ctrl+C and Ctrl+V. `LOOKUP( Date , GROUP( SLICE( A:A ) ), results )`
With SLICE( A:A selected click Wrap current value in function, Data Manipulation, SELECT.
This lets you create a condition statement.
`LOOKUP( Date , GROUP( SELECT( SLICE( A:A , start , end) , ( condition ))), results )`
With condition selected click the column containing the records. LOOKUP( Date ,
GROUP( SELECT( SLICE( A:A , start , end;) , (B:B ... ))), results )
With B:B selected click Wrap current value in function, Data Manipulation, SLICE.
LOOKUP( Date ,
GROUP( SELECT( SLICE( A:A , start , end;) , ( SLICE( B:B ... ))),results ))
With the first ... selected click the = operator. LOOKUP( Date ,
GROUP(SELECT( SLICE( A:A ) , SLICE( B:B ) = ... ) ) ), results )
With first ... selected click Insert literal string or number, type Yes as it appears in the data source, and then click the Apply button. LOOKUP( Date ,
GROUP(SELECT( SLICE( A:A ) , SLICE( B:B ) = "Yes" ) ) ), results )
With GROUP selected copy and paste the GROUP formula into results and change GROUP to GROUPBY. LOOKUP( Date ,
GROUP(SELECT( SLICE( A:A ) , SLICE( B:B = "Yes"))),
GROUPBY( SELECT( SLICE( A:A ) , SLICE( B:B )= "Yes") ), measure, method )
With SELECT selected copy and paste the SELECT formula into measure. These are the values that you want counted. LOOKUP( Date ,
GROUP(SELECT( SLICE( A:A ) , SLICE( B:B = "Yes"))),
GROUPBY( SELECT( SLICE( A:A ) , SLICE( B:B )= "Yes") ), SELECT( SLICE( A:A ) , SLICE( B:B )= "Yes") ), method )
With method selected click Insert literal string or number, type
count(values), and click the Apply button.
LOOKUP( Date ,
GROUP(SELECT( SLICE( A:A ) , SLICE( B:B = "Yes" ))),
GROUPBY( SELECT( SLICE( A:A ) , SLICE( B:B )= "Yes") ), SELECT( SLICE( A:A ) , SLICE( B:B )= "Yes") ), "count(values)")
In the formula editing toolbar click LOOKUP, click Evaluate Current Session to verify the formula is working correctly and click the Finished button. Result: `2, 1, space, 1, space, space, space`
Select the Properties panel. Provide a name for the series using the Series Label. Yes
From the Preview window. Add a new series by clicking the + Series.
From the component tree select Series: Untitled and copy and paste the LOOKUP formula from the 1st Series. To do this, use Ctrl+C and Ctrl+V.
Change all occurrences of Yes to No. This creates the other condition of No.
Select the Properties panel and provide a name for the Series Label.
No
From the component tree select Y-Axis: Untitled and provide a name for the Axis Title. Daily count
click Save.

## Step 3: Alternative formulas

If you are building this Klip, you can also use this formula that uses the CONTAINS function to select desktop.

Where A:A is the date (20131222), E:E is the device (desktop), F:F is the number of visitors (1) and Date is a reference to the x-axis.