Combination Chart - For Comparing Revenue with Percentage of Profit (dynamic data)

In this section we will show you how to create a dual Y-axis Combination Chart which compares a company’s revenues with the profit percentage; the company’s revenues will be plotted using columns and the profit percentage using lines, both on separate Y-axes, as the data series contain different magnitude values.

As the target data is dynamic, it will be retrieved from two database table entitled Revenue and Profit Analysis.

Click here to download the database used to create this chart

The Revenue table contains an entry for each income received by the company; the income is described by the following fields: a unique ID, the income source name, the value of the income and the date when the income appeared. The Profit Analysis table contains an entry describing what percentage of the income is profit; the values are described by the following fields: a unique ID, the percentage and the date.

In order to obtain the data needed to plot the income and profit percentage in by the company, we will add two data series from the tables described above – the first one from the column describing the income values in the Revenue table and the second one form the column describing the profit percentage from the Profit Analysis table. The series will be grouped “by month” and displayed “by quarter“, and a sum will be applied on the data values for each quarter; all SQL queries needed to organize the data will be automatically generated based on a few options configured from the FusionCharts for DW user interface. The target data based on which the chart will be plotted is displayed in the table below.

Following steps will guide you through the process of creating a Combination Chart:

1. By default, the Data tab of the FusionCharts for DW user interface opens. Select Automatically Generate SQL Queries to us data from the tables in a database.


Select the data source

2. From the list labeled Connection, select combination (the connection to the database must already exist in order to do this step). Select the plot type as Data Grouped By Date, then click Next.


Select the connection and the plot type

3. From the Category Data tab, configure the Data Grouped By Date options; select Quarter for the Group By option and Quarter 1 for the Display Format option. Leave the Min Value and Max Value withe their default values, 0 and 4; finally, leave the Plot Complete Date Information option disabled. Click Next to configure the chart series data.


Configure the category data

4. From the Series Data tab, click the Add Series button to open the Add/Edit Series Form. Configure the Series options; select revenue for the Data Source option, revenue.IncomeValue for the Value Column option, sum for the Calculation option, revenue.IncomeDate for the Date Column option and YYYY-MM-DD for the Date Column Format option. Finally, click the OK button to add the data series.


Add a data series



Configure the data series

5. Repeat the preceding step to add another series. Configure the Series options; select profitanalysis for the Data Source option, profitanalysis.ProfitPercentage for the Value Column option, sum for the Calculation option, profitanalysis.Date for the Date Column option and YYYY-MM-DD for the Date Column Format option.
6. In order to see the data which will be plotted in the chart before configuring the chart options, use the Preview Data option. If you have any difficulties understanding the steps above, which refer to configuring the dynamic chart data, please read the Configuring Dynamic Chart Data section of the documentation.
7. Switch to the 'Chart Selection' screen and select the '2D Dual Y' chart as your chart type.
8. Notice - that a 'Render Series Options' section has been displayed in the 'Chart Selections' screen. In this section you can choose the type of data plot for each series and also select a Y axis for each series. The Y axis that is displayed on the left side is referred to as primary Y axis and the one which is displayed on the right side is referred to as secondary Y axis. For our example we will be plotting 'Revenue' as 'Columns' along the primary Y axis, and 'Profit %' as a 'Line' along the secondary Y axis. The following steps show how to choose a data plot and an axis:
   

   • For column 'B' - Click the 'Render As' button and select 'Columns' from the drop down list, next click the 'Render Axis' button and select 'Primary' from the drop down list.
   • For column 'C' - set the 'Render As' parameter to 'Line' and the 'Render Axis' parameter to 'Secondary'.
9. In the 'Titles & Axia Nmaes' panel - enter 'Quarter' in the 'xAxisNmme' field, 'Revenue' in the 'PYAxisName' filed and 'Profit %' in the 'SYAxisName' field.
10. Check the 'Show Advanced Options' check box which is located in the upper portion of the 'Chart Options' screen. Scroll down a bit and open the 'Chart Limits' panel by clicking on it and set the 'SYAxisMaxValue' to 100. By doing this we have set the maximum limit of the secondary Y axis to 100.
11. Switch to the 'General Options' tab and set the chart name to 'myfirstCombiChart', and then set the height and width to 400 pixels and 300 pixels respectively.
12. In the 'Caption, Subcaption' panel - enter ''Financial Analysis for 2008' in the 'Caption' field and 'Revenue vs Profit %' in the 'Subcaption' field.
13. Check the 'Show Advanced Options' check boc which is located in the upper portion of the 'General Options' screen. Now, go to the 'Number Formatting and Scaling' panel and enter '$' into the 'NumberPrefix' field and '%' into the 'sNumberSuffix' field.
14. Preview the chart using 'Open Preview' button and then click 'OK' to embed the chart into your web page.

You can now open the page in a browser to see your fist combination chart in action.