Building a bar/line chart with a secondary y axis

A bar and line chart allows you to show one or more series of data as a bar chart, line chart, or combination of both. For this example, actual and budgeted sales are displayed as different colored bars, and the number of website visitors is brought in using a Google Analytics data source and displayed as a line using a different y axis.


You can click the following links to download the training data source files.

https://static.klipfolio.com/images/saas/ACME Foods Income.xlsx
https://static.klipfolio.com/images/saas/Google Analytics Data Source.csv

Building and configuring a bar/line chart

  1. Click the Build a New Klip button.
  2. Select the Bar/Line Chart component.
  3. Select Use an existing data source from the library and select one of our training sample data sets.

    Tip: You can add the other data source by clicking Add Data source within the Klip Editor.

  4. Select the Klip component and in the Properties panel, enter a name for the Klip Title. For example, Sales and Marketing Performance YTD.

Location

Action

For our example

Using the Acme data source, Actuals spreadsheet, with the X Axis sub-component selected, select the Data panel. From the data source, select all the months, by selecting row 3. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan-Dec
With 3:3 selected in the formula editing toolbar. Select Wrap current value in function, Data Manipulation, SLICE SLICE( 3:3 , start , end )
With start selected. Select Insert literal string or number, type in 2, and click the Apply button. SLICE( 3:3 , 2, end )
With end selected. Select Insert literal string or number, type in 14, and click the Apply button. SLICE( 3:3 , 2, 14 )
Select the Properties panel. For Axis Title, type Date and select Show axis title.
Select the Data panel. In the formula bar, click on the word SLICE and copy the formula using the key combination Ctrl+C.  
Select the Series: Untitled sub-component, Data Panel. In the formula bar, paste using the key combination Ctrl+V. SLICE( 3:3 , 2, 14 )
Select Insert data from data source button and select the Budget worksheet. In the formula bar, select 3:3, and select the total budgeted sales, row 10. SLICE( 10:10 , 2, 14 )
Select the Properties panel. For Series Label, type Budgeted Sales and override the default color by selecting the color light grey.  
Add another series by selecting + Series and select the Data panel. In the formula bar, paste the content previously copied using the key combination Ctrl+V. SLICE( 3:3 , 2, 14 )
Select Insert data from data source button and select the Actuals worksheet. In the formula bar, highlight 3:3, and select total actual sales, row 10. SLICE( 10:10 , 2, 14 )
Select the Properties panel. For Series Label, type Actual Sales and override the default color by selecting the color dark grey.  
Select the Y Axis, Properties panel. For Axis Title, type Sales, select Show axis title, Format as: Currency, and for Suffix type K.
Add another series by selecting + Series, select Insert data from a data source, and select the Google Analytics data source. In the formula bar, select Column B. B:B
To remove the row header, in the formula bar, select B:B. Select Wrap current value in function, Data Manipulation, SLICE SLICE( B:B , start , end )
To add a secondary axis, select + Y-Axis. For Axis Title, type Website Visitors, select Show axis title, and change the axis position.  
Select the Series: Untitled, Properties panel. For Chart type, select a line. For Series Label, type Website visitors. For Use Axis, choose Website Visitors.  
  1. Click the Save button.
  2. Click the Finished button.

Feedback and Knowledge Base