Pivot Data Source

Introduction

Pivot operation can be used to summarize data. A simple case would be that we have a text file containing item type and number of copies sold in each line. Pivot operation can be used to summarize how many different kinds of items we have sold.

Examples

Default pivot operation, summarizing number of items sold. The source data consists now of the following text file:

#item quantity
axe 2
saw 3
hammer 4
axe 5
axe 6
saw 2 
hammer 1

The text file is imported using CSV data source. To perform pivot operation to the imported data, we enclose importing CSV data source, by <pivot> elements:

<chart>
  <data>
    <pivot>	       
      <csv source="http://localhost:8080/chart/usersguide/datasources/pivot/soldItems.csv" />
    </pivot>
  </data>
  <attributes>
    <plot paint="default"/>
    <axes> 
      <axis dim="y" min="0"/> 
      <axis dim="x" titleText="Number of items sold on week 33"/>
    </axes>
  </attributes>
</chart>

Default pivot operation from simple data.

Show XML Edit Chart

Assume that we have also time information on each row. Thus data looks similar to:

#item quantity date
axe 2 19.3.2003
saw 3  20.3.2003
hammer 4  20.3.2003
axe 5  24.3.2003
axe 6  24.3.2003
saw 2  25.3.2003
hammer 1  26.3.2003
...

Date will be now used as the category, items sold as the series and value, for each (category - series) pair, will be quantity. In XML configuration:

  <chart>
     <data>
	<pivot>	       
	  <pivotSeries column="item" />	
	  <pivotValues column="quantity" />	
	  <pivotCategories column="date"/>
	  <csv source="http://localhost:8080/chart/usersguide/datasources/pivot/soldItems2.csv">
 	  	<csvColumn column="1" id="item"/> 
	  	<csvColumn column="2" id="quantity"/>
	  	<csvColumn column="3" id="date" type="value" dataType="date" dataFormat="dd.MM.yyyy"/>
	  </csv>
        </pivot> 
     </data>
     <attributes>
	<legend alignment="up" />
       <plot paint="default"/>
       <axes> 
	<axis dim="y" min="0"/> 
	<axis dim="x" titleText="Number of items sold on weekly basis"/> 
	</axes>
     </attributes>
   </chart>
</pre>

Pivot operation results into a value for each category-series pair.

Show XML Edit Chart

One can also observe sales on weekly basis by defining a step for <pivotCategories>. In the chart weeks are named by their start date. The step could also be something like 2days or 4months.

Aggregating days using <pivotCategories step="week">.

Show XML Edit Chart