The Time Series Dataset

The Time Series Dataset

A Time Series Dataset plots pairs of values as points on the chart. The domain values (commonly the x-axis values) are date or time based values, and the range values (commonly the y-axis values) are numeric values. The time series charts have one numeric axis (the range axis) and one time-based axis (the domain axis).

If the dataset is arranged by column, each row is expected to have three columns. Each row is assumed to represent one data point on the chart, ordered by the series name and domain value. The first column's values contain the name of the series. The second column is a date/timestamp, containing the domain value of the data point. The third column is numeric, containing the range value of the data point.

If the dataset is arranged by row, each row is expected to contain all the data points for the series. The first column's values contain the name of the series. It is assumed the other columns in the row consist of a collection of x/y data points (i.e. columns 2, 4, 6 etc. will be domain values (dates), and columns 3, 5, 7 etc will be range values(numbers)).

The date values in the dataset must be java.util.Date objects. If you are building your dataset from a SQL query, and the data is represented in the database in a Date column, then the date values will automatically be Date objects. if you are building a resultset manually (ie., with a Javascript rule), make sure that you are adding the date data as java.util.Date objects.

A Column-based Dataset Example

Here is a query and dataset from the Pentaho sample data that demonstrates a column-based time series dataset. This query works specifically with the Hypersonic sample data database.

SELECT PRODUCTLINE, ORDERS.ORDERDATE, SUM(ORDERDETAILS.QUANTITYORDERED*ORDERDETAILS.PRICEEACH) SOLD_PRICE
FROM ORDERS INNER JOIN ORDERDETAILS ON ORDERS.ORDERNUMBER = ORDERDETAILS.ORDERNUMBER
INNER JOIN PRODUCTS ON ORDERDETAILS.PRODUCTCODE =PRODUCTS.PRODUCTCODE
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERNUMBER =CUSTOMERS.CUSTOMERNUMBER
INNER JOIN EMPLOYEES ON CUSTOMERS.SALESREPEMPLOYEENUMBER = EMPLOYEES.EMPLOYEENUMBER
INNER JOIN OFFICES ON EMPLOYEES.OFFICECODE=OFFICES.OFFICECODE
WHERE  (ORDERS.ORDERDATE >= '2005-01-01' AND ORDERS.ORDERDATE <= '2005-06-30') AND PRODUCTLINE IN ('Classic Cars', 'Motorcycles', 'Planes', 'Ships')
GROUP BY  PRODUCTLINE, ORDERS.ORDERDATE
ORDER BY PRODUCTLINE, ORDERS.ORDERDATE

This query results in the following dataset (the data has been truncated to save space):

Handing this dataset to a chart, the chart will plot the data by column by default. Here is a time series line chart example:

A Row-based Dataset Example

To plot the data by rows, you must add the by-row property to the action sequence component definition. (You can set this property in the Design Studio by checking the Chart Row Dimension checkbox.) Also, your data must be arranged in row format.

<by-row>true</by-row>

Unlike the categorical dataset, arranging the dataset by rows does not change the plotting of the data on the chart. The exact same chart as above would be generated by this dataset: