Interactively build pivot tables from variables, visualize as bar-charts, line-charts etc. Define ad-hoc summations and assess distributions as box plots.
This cockpit allows you to aggregate one or more variables for any combination of categories (e.g. averages, sums, etc. per month, day of week, year, etc.). The aggregates per category are displayed as table, bar charts, line charts, etc.. By selecting multiple variables, it is possible to interactively create totals of the variables, for example to try out how the grouping of the variables affects the total sum (e.g. of output or production). The aggregates can be exported in various formats for further processing with one click.
- Select variables to be aggregated in the Pivot summaries, and displayed in the other diagrams.
- Summaries of the selected variables: Views that aggregate the values of the selected variables by categories and display them as table, bars, line charts or box plots.
- Drill Down: In this cockpit, mainly used to select categories or time periods that should be the basis for the summaries.
- Time Series: Time series plots of the selected variables with the possibility to select records, and to zoom to selected time ranges.
- Table: A table-like view of single values that can be shown on demand.
A list of all variables, with the main purpose of selecting variables to be shown in the other views. The list can be filtered to those containing a certain text. The list can be ordered by various statistics by clicking on a column header.
Summaries of selected variables
These views show statistical summaries of the selected variables, considering the data records that are in focus. All views allow further subdivision by categories, to interactively build pivot tables, bar charts, etc. with a single click.
- Pivot table: Classical pivot table for the selected variables. Select the statistical aggregates, and the categories to build the table by clicking the axis labels.
- Multi-Variable Bars: The lengths of the bars display one statistical aggregate (initially: mean value) per category, for each selected variable. Select the statistical aggregate, and the categories to build the table by clicking the axis labels. Special case: select the "Sum" aggregate to get an additional 'total' bar per category that sums up all selected variables - to support an interactive summation use case.
- Line graphs: Analogous to the multi-variable bars, but displaying connected lines.
- Box plots: Encodes not only a single statistic per category, but the distribution of values within each category as a box plot (i.e., median, interquartile range, and 2.5 and 97.5 percentiles). The widths of the boxplots are proportional to the number of entries per category.
- Variable bars: Variation of the "Multi-Variable Bars" chart, where the selected variables are not distinguished by color, but by extra labels on the x axis. This works better for large numbers of selected variables, as colors would be only distinguishable up to ~12 categories.
Exporting: A key use case of these views is exporting the underlying pivot tables. To export the current view as table, click the diagram title, then click the symbol shown on the left.
These diagrams initially display the mean value of one selected variable per category, using colors. Each colored cell (or bar) aggregates the data that falls within the corresponding (combination of) categories.
- Calendar: is only available if a Date/Time-typed data attribute is present. It shows one cell per day.
- Pivot Table: shows multiple statistics per (combinations) of categories. The categories can be selected via dropdown-menu when hovering the axis label.
- Bar Chart: a bar chart based on (combinations) of categories. The bar length encodes the number of data records per category. The categories for the bars can be selected via dropdown-menu when hovering the axis label.
- Heatmap: a grid-based, matrix display, built using (combinations of) categories on both the horizontal and the vertical axis. The size of cells encodes the number of data records per category combination. The categories used for building the chart can be selected via dropdown-menu when hovering the axis labels.
- Categories: Similar to 'Heatmap', for up to four categorical attributes at once.
The views can be used to perform selections of categories by clicking the category labels, which defines a focus for the other views. If a focus was already defined in other views, the Drill-Down views only consider the data records of the focus.
Instead of showing the mean value of a selected variable, a drop-down menu available when hovering the color legend allows to choose to color by different statistics.
These plots show variables over time. If the time axis has a regular raster, the timeline is connected as line graph. If more than one variable is shown, there are two ways of distinguishing them:
- The tab Time Series plots them in the same graph, and distinguishes them by colors. Instead of displaying individual scales for each variable, you can select a common scale, or normalize the values for display, by clicking the y-axis label, and selecting a different option in the drop-down.
- The tab Time Series (stacked) shows them side by side (limited to 5 variables at a time).
- Zooming: Drag a rectangle with the right mouse button to zoom in. Alternatively, use CTRL + mouse wheel to zoom in or out. Once zoomed in, there is a button in the bottom-left corner of the diagram with arrows pointing outwards. Click it to zoom out completely again.
- Select time periods by dragging the left mouse button to put them into focus for the other views.
- Adjust axis range by clicking the top/bottom label of the axis. Note: if you want to adjust multiple axes, adjust them from left to right.
The diagram offers several options when clicking the view title "Time Series". The most important ones are:
- Selection mode: changes the tool for selecting data records in the view. Available alternatives to the 2D rectangle are 1D intervals, or a free-form Lasso selection.
- Trend overlays: In addition to the displayed time series, a smoothed version of the time series can be displayed to analyze long-term trends. This is done by calculating a moving average over adjacent time points.
- Automatic zooming: enabling this feature will let the time series zoom automatically to the focus, whenever a new focus is defined.
Table ('Focus data records')
A table showing the single values. Only considers the data records in focus. Click the header of a column to sort the table by it. To change the set of displayed columns, click the header "Shown: 103 of 210 data attributes". Click single rows or drag a line with the left mouse button to select records, putting them into focus.
Exporting: A key use case of the table is exporting a selected subset of the data. To export the current state of the table, click the view title "Focus data records", and then "Data export".
Roles supported by this cockpit
The following roles can be given to data attibutes in this cockpit. Use the icon in the toolbar to adjust them.
- Time axis: This role can be given to a data attribute defining a temporal ordering of the data records. Can be time stamps, or values. It will be used to define the temporal context for all variables, e.g. the times of measurement, consumption, production, etc. If the role is assigned to a data attribute of date/time type, periods like 'Month', 'Hour' etc. are extracted and available for defining filters, and categorical plots like bar charts.
- Variable: Numerical data attributes with this role can be inspected in the cockpit and are considered in calculations. In case you want to exclude a variable from all considerations, simply do not assign this role to it.
- Category: Some views aggregate values by categories (e.g. per day of week, per month, etc.). When this role is assigned to a categorical data attribute, its categories are available for such aggregations. Example: Assigning this role to a 'Holiday' variable with categories 'yes' and 'no' allows comparing values like energy production for holidays vs. non-holidays. The role can also be assigned to numerical data attributes, which results in one category per distinct value of that attribute (e.g. different states encoded as 0 or 1). If the role is given to a data attribute of date/time type, periods like 'Month', 'Hour' etc. are extracted and available for defining filters, and categorical plots like bar charts.
- Upper limit (Visplore Professional only): A variable with this role defines an upper limit for another numerical variable. It is shown along with the referenced variable in time series views.
- Lower limit (Visplore Professional only): analogous to Upper limit, but for lower limits.
- Setpoint (Visplore Professional only): analogous to Upper limit, but variables with this role represent a setpoint (=desired state) for the reference variable.
License Statement for the Photovoltaic and Weather dataset used for Screenshots:
"Contains public sector information licensed under the Open Government Licence v3.0."
Source of Dataset (in its original form): https://data.london.gov.uk/dataset/photovoltaic--pv--solar-panel-energy-generation-data
License: UK Open Government Licence OGL 3: http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/
Dataset was modified (e.g. columns renamed) for easier communication of Visplore USPs.