Creating a simple and a more complex bar chart

It is easy to build Klips. In this article, let's build two examples: a simple bar chart using the SLICE function that will include a selection of data, and a more complex bar chart using the GROUP and GROUPBY functions to ensure that the date and number values are aligned with those in the data source.

 

 

The following formulas are part of each Klip.

Simple Klip

Axis: Date

SLICE( B:B , start, end )

Series: Total number of visits

SLICE( C:C , start , end )

Complex Klip

Axis: Date

GROUP( CONCAT( SLICE( B:B , start , end ) , “-“ , SLICE( A:A ) , data ) )

Series: Total number of visits

GROUPBY(CONCAT( SLICE( B:B , start , end ) , “-“ , SLICE( A:A ) , data ) , SLICE( C:C ) )

These Klips use the following Google Analytics data set.

  A B C
1 ga:year ga:month ga:visit
2 2013 01 3368
3 2013 02 3145
3 2013 03 3397
4 2013 04 3609
5 2013 05 4277
6 2013 06 41

Step 1: Building a simple chart

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select Use an existing data source from the library.
  4. Select your data source file.
  5. From the component tree, select the Klip node.
  6. From the Properties tab, type a name for the Klip Title.

Note: For our example, Simple bar chart

  1. Select the Series: Untitled sub-component, select the Properties tab, and provide the following information:
        Name for
    Series Label
        Select the box
    Show values on chart
        Select the box
    Leave gaps for blank values

Note: For our example: Total number of visits

  1. Click the Save button.
  2. Type a name for your Klip.
  3. Click the Finished button.

A note about SLICE

You can select a portion of data from a column using the SLICE function.

For example, SLICE( B:B , -5 , end ) and SLICE( C:C , -5 , end ) returns 5 months of data counting from the end of the column, i.e. January is not included. And, SLICE( B:B , 1 , 3 ) and SLICE( C:C , 1 , 3 ) return 2 months of data counting from the start of the column, I.e. January and February. When the whole column is selected, as in this example, the data automatically updates when the data source refreshes to include the data for the next month.

Step 2: Building a more complex chart

    1. Select Build a New Klip.
    2. Select the Bar/Line Chart component.
    3. Select Use an existing data source from the library.
    4. Select your data source file.
    5. From the component tree, select the Klip node.
    6. From the Properties tab, type a name for the Klip Title.

Note: For our example, Complex bar chart

    1. Select the Series: Untitled sub-component, select the Properties tab, and provide the following information:
          Name for
      Series Label
          Select the box
      Show values on chart
          Select the box
      Leave gaps for blank values

Note: For our example, Total number of visits

    1. Click the Save button.
    2. Type a name for your Klip.
    3. Click the Finished button.

A note about the DATE format

The date format is important and varies from data source to data source. In some data sources, the date format might be 2013-05-31 12:30:00. If you do not want the HH:mm:ss included, you must wrap the date in either the LEFT or SUBSTRING functions and specify the number of characters.

For example, SUBSTRING( B:B , 1, 10 )

Tip: To verify the date in your formula try using the Evaluate the current selection button.

A review of functions in these Klips

SIMPLE KLIP

Comment on Series
The SLICE function removes the row header AND can specify a portion of data by using start and end.

COMPLEX KLIP

Comment on X Axis
The CONCAT function lets you join two values together like month and year.
The GROUP function returns a single value for each month of the year.

Comment on Series
The GROUPBY function totals the number of visits for each month. The default value for GROUPBY is SUM.

A note about the GROUP or GROUPBY function

If you use the GROUP function for the X Axis sub-component, you must start the formula for each Series sub-component with the GROUPBY function.

For example:

Our X Axis is: GROUP( CONCAT( SLICE( B:B ) , “-“ , SLICE( A:A ) , data ) )

Our Series sub-component must therefore start with: GROUPBY(( CONCAT( SLICE( B:B ) , “-“ , SLICE( A:A ) , data ) )

By doing this, you ensure that the date and number values match those in the data source, otherwise there might be a mismatch.

Tip: Use the X Axis sub-component, Properties tab, to apply a Sort Order.