Measures and Dimensions

Special Behavior with Table Charts, for Measures

Table charts are a nongraphical, row/column presentation of raw data. Measures can behave differently than with other chart types. If you create a table chart with measures but no dimensions, the data is not grouped; the chart presents raw information at the row level from the database. If you create a table chart with dimensions, the measures act in their usual way, as an aggregate calculation. If you want to view raw, row-level information from the database without performing any transformations or calculations, create a table chart with measures but no dimensions.

Custom Measures

In addition to normal measures, which perform simple aggregation calculations on data, you can create custom measures that perform aggregations for any OmniSci-supported SQL. For information about OmniSci SQL support, see OmniSci SQL Capabilities.

The custom measure can be any aggregate statement that can be substituted for the term COUNT(column1) in the following SQL statement.

SELECT column1, COUNT(column1) FROM table WHERE column1='foo' GROUP BY column1

To create a Custom Measure:

1. Create or edit a chart.
2. From the Add Measure pop-up, choose Custom SQL Measure.
3. Enter an Alias for the custom measure.
4. Enter the SQL aggregation clause in the Custom Measure field.
5. Click OK.

For example, if you have a table with Price, Quantity, and Tax columns, you can compute the Subtotal using the custom SQL clause (Quantity * Price) + (Quantity * Price * Tax).

You can check to see which products bring in the most average gross sales by finding the average of the Price.

Because most Immerse charts require dimensions, which group the data, custom measures for those charts must always be written as aggregate statements, as in the examples above. However, for charts that do not require dimensions, such as a point map or table, you can write custom measures that are not aggregate statements.

Binned dimensions

When a dimension is set to a numerical column, Immerse presents information grouped by each number. If there are many numbers, Immerse automatically creates binned ranges of numbers. For example, if you have a table with many distinct numbers from 1 to 1 million, a display of 10 bins would be 1 - 100,000, 100,001 - 200,000, and so on.

You can manually disable automatic binning to force the display of all numbers in the dimension. Depending on the expense of the query, Immerse might display a notification that the unbinned query is not available.

Null Dimensions

Many times, measures have no entry for a dimension. A null value might or might not be significant. For the measure Baggage_Fee, a null value might be considered “no fee collected,” and could be useful information. For the measure Departure_City, a null value is spurious data, because an airline flight has to take off from somewhere. Spurious data can skew the accuracy of your chart.

You can choose to omit null dimensions from Table, Bar, Bubble, and Heatmap charts to visualize only existing data values.

Custom Dimensions

You can create custom dimensions that group query results by using OmniSci supported SQL. Consider the following standard SQL query. You can create a custom dimension using any statement that could replace the BY clause in this SQL query:

SELECT column1, COUNT(column1) FROM table WHERE column1='foo' GROUP BY column1

To enter a Custom Dimension:

1. Create or edit a chart.
2. From the Add Dimension pop-up, choose Custom SQL Dimension.
3. Enter an Alias for your custom dimension.
4. Enter the contents of a BY clause from a SQL query into the Custom Dimension field.
5. Click OK.

For example, looking at a dataset of voters from the 2016 election, you might want to do some demographic analysis by gender. You can build your BY clause by adding the Gender column name. This is no different from setting the dimension to Gender, but it has the bonus side effect of changing the title of the column in your chart.

You might want to express the results as how many voters identified themselves as female. The custom SQL clause is 'f'. The Alias is Women Voters. The information is presented as true if the gender is female, or false if the gender is not.

You can create a new dimension combining information from two or more columns. For example, you can use a case statement to define a custom dimension that groups by both gender and party affiliation.

case
when Parties like 'Female Democrats'
when Parties like 'Male Democrats'
when Parties like 'Female Republicans'
when Parties like 'Male Republicans'
when Parties like 'Female Independent'
when Parties like 'Male Independent'
when Parties like 'Female Bull Moose'
when Parties like 'Male Bull Moose'
else 'Other' end

You can also use the case statement to create a funnel that aggregates data at an increasing level of specificity. For example, this SQL clause creates a chart that shows the number of UFO sightings based on an accumulation of common factors.

case
when comments ilike 'Dancing, bright light'
when comments ilike 'Bright light'
when comments ilike 'Light'
else 'NA'
end

Common Number Formats

Examples

Line Chart Date and Numbers

The following Line chart shows sales by order date:

To make the chart easier to read, apply the format %m/%d/%y to dimension date formatting and SI to measure number formatting:

Bar Chart Percentage

The following Bar chart shows profit ratio percentage to six decimal places, with % applied for Custom Profit Ratio:

To simplify the percentage format to one decimal place, apply ,.1% to the Custom Profit Ratio:

Bar Chart Currency

In the following Bar chart, the average profit by state is expressed in whole dollars, both positive and negative, by using the ($0.f format in the Avg Profit input box: