# Chart Editor

The chart editor allows the user to create charts based off of their data that they have uploaded into the system. It has three main parts The Data Explorer (bottom left), the query editor (top) and the chart preview (bottom right):

Chart Editor

Description of these main sections are below.

# Data Explorer

Data Explorer

The data explorer allows the user to drag data block to the query editor so that they can be used in the query for the chart

From top to bottom displayed is:

  • Search box - used to filter data items by name
  • Datatype tab filters - select the tabs to filter the datatypes shown by type
  • Datasource drop-down - used to select the datasource
  • Sheet drop-down - used to select the sheet

Before any datatypes are shown a datasource and sheet must be selected. once shown they can be filtered using the search box and or the datatype tabs.

To use a datatype in your query drag and drop the data item to the aggregate, group or filter drop boxes in the query editor.

# Query

The query editor has three drop boxes that define three stages of a query:

  • Aggregate - used to create a numerical value used as individual point on the chart
  • Group - used to group the data used on the chart
  • Filter - used to filter data used on the chart

Grouping and filtering are optional but an aggregate is not optional and so it is recommended to create the aggreate first before grouping / filtering the data.

# Aggregate

There are three categories of aggregate:

  • Standard - These are builtin function like Sum, Average, Count which are described below
  • Unaggregated - When selected the group settings will be ignored and this will returned raw data. This is useful for data tables to show raw data for charts
  • Custom - Allows the user to create completely custom calculations again this is described in more detail below

These are accessed by clicking on the down arrow on the aggregate item. The name of the aggregate can be change by clicking on the textbox after the as text:

AggRename

# Standard Aggregate Functions

The standard aggregate functions available are:

Function Name Applicable Data Types Description
Count Number, Text and Date A count of the number of these records.
Sum Number Arithmetic sum of the values.
Average Number Arithmetic average of the values.
Median Number Arithmetic median of the values.
Minimum Number Lowest number of the values.
Maximum Number Highest number of the values.
Variance Number Variance of the Values.
Standard Deviation Number Standard Deviation of the values.
First Number, Text and Date First value of the set of values.
Last Number, Text and Date Last value of the set of values.

# Unaggregated

The user can select Unaggregated as an aggregation function. This returns the dataitem without any calculations done but also ignores any grouping selected within the query as the idea is to show raw data rather than grouped data.

# Custom Aggregate

Custom allows the user to create their own aggregation functions. The aggregation pipeline uses mongodb Aggregation Pipeline Operators (opens new window). The user enter the custom function within the Edit Custom Formula in JSON format:

CustomAgg

The screen shot above shows how to create a count custom function as it sums 1 for every row in the data set.

You can also create a custom group by using the Custom Aggregation to do so split the json into two fields $projection and $group the $projection is the mongodb Projection (opens new window) used in the query and the $group is used in the Group (opens new window) part of the query.

If you do not include $group or $projection in the query then the query will be used as projection only.

This is a complex feature fo the software and so it is understandable if help may be required when creating Custom Aggregations please send an email to [email protected] containing what you are trying to do and we will guide you through creation of custom aggregations.

# Group

The user uses group to group the data together to allow for aggregation. An example is if you want you sales data grouped by region then you would drag the region field up to the group drop target. The name of the group can be change by clicking on the textbox after the as text:

GroupRename

For Dates there are built in operator that will transform the date of the item before grouping which is accessed by clicking the down arrow on the group data item box. The date functions are:

  • Second - The full date time down to the second
  • Minute - Date time down to the minute
  • Hour - Date time down to the hour
  • Day - Date only down to the day
  • Month - Date only down to the Month
  • Quarter - Quarter and year e.g. Q1 2020, Q2 2020
  • Year - 4 digit year
  • Day of Week - Day of the week in text e.g. Monday, Tuesday ...etc
  • Day of Month - Day of month as integer
  • Day of Year - Day number (opens new window) of year as integer

The group drop target also indicates the sorting details. These are accessed by clicking the arrow at the end of the group item which will open the Sort Settings popup:

SortSettings

You can sort by the values that come out of the aggregate operation or that come out of the group operation. This is selected by using the Sort Type drop-down. If the sort type is selected to Aggregate then you must select which aggregate you wish to sort on with the Value drop-down.

Direction of the sort is selected with the Sort Direcion drop-down.

# Filter

The user is able to filter data before any grouping or aggregation. Similar to Aggregation and Grouping drag the data item you wish to filter on from the Data Explorer to the filter drop box to star filtering. Clicking on the down arrow on the filter will bring up a drop-down that will show the filter settings:

FilterDropdown

The filter operator is shown as two boxes labeled AND and OR. This allows you to specify if the data passed all filters or any filters. The following filter settings are available:

  • Equals
  • Not equals
  • Is in
  • Is not in
  • Like
  • Not like
  • Regex
  • exists
  • Not exists
  • limit

For date data items the filter can be set to use the dashboards date. This will show a date filter on the dashboard that allows the user to filter data on their own. A modifier can also be added to this to allow comparison of data over different data ranges that are set by the user.

For the exists filter GravaMetrics can only exclude / include whole rows not individual columns. With no exists filter set we do not check for existence and so the count aggregation is all the rows regardless of how many columns they have.

There is a hard limit on all queries to 250 aggregated results and 1000 unaggregated results. This is because the rendering of graphs with large number of results will cause the browser to hang so this is stopped by the query.

# Preview

This area of the chart Editor gives a preview of what the Graph will look like when displayed on the dashboard.

Chart Preview

The button with the cogs icon opens the Chart Settings popup and the buttons below the preview allow changing of the chart type.

# Chart Type

The chart type can be changed by clicking on the buttons below the chart. Certain queries will allow certain chart types. This is automatically detected by the chart editor and text will be displayed if there is a problem with the query and the currently selected chart type. Also only the appropriate chart types are enabled so if your query has a problem click on one of the enabled chart types.

A list of chart types is as follows:

  • Table - Table of data, available for all valid queries
  • Value - Single numerical value, available if only aggregate is selected
  • Bar - Bar or Column chart, can also be stacked and 100% stacked. Available if grouping is selected
  • Line - Line chart, available if grouping is selected
  • Area - Area chart, available if grouping is selected
  • Funnel - Funnel chart, available if grouping is selected
  • Pie - Pie chart, available if grouping is selected
  • Doughnut - Doughnut chart, available if grouping is selected
  • Scatter - Scatter chart, available if grouping is selected
  • Gauge - Gauge chart, available if grouping is selected
  • Bubbles - Bubbles chart, available if grouping is selected
  • Polar - Polar chart, available if grouping is selected
  • Map - Map chart, available if grouping is selected. Grouping must be either US state name or Country name.

# Chart Settings

The Chart Settings popup is accessed through the cogs icon on the preview panel.

Chart Settings

There are a great many chart settings and rather than go through all these settings exhaustively the general headings for settings are explained here:

  • General - Settings often very specific to the chart type like coloring options, number formatting
  • Title - Options for the title that is automatically added to the chart
  • Axis - X/Y Axis settings only displayed if a chart has X/Y axises
  • Legend - Settings for the legend of the chart only available for charts that have legends
  • Tooltips - The hover over tooltips that are displayed for the chart if applicable
  • Layout - Padding settings for the chart
  • Animation - Animation duration and easing mode. Set Duration to 0 to turn off animations

TIP

It is recommended to try changing the setting of the chart yourself to see what affect they have on the chart. A full written explanation of each of these settings is often more confusing that just changing the settings and observing the change in graphical behaviour