Variables


Chartio offers two types of Variables: Dashboard Variables and Drilldown Variables.

Dashboard Variables allow you to filter multiple charts at once from your dashboard, using a text input, calendar picker, or other input type.


Drilldown Variables allow you to use charts as filters for other charts. Simply link the charts together, and click an area in a chart to drill down into your data. You can even link to another dashboard.

Dashboard Variables

Dashboard variables are variables you can set on a dashboard-wide basis. They allow you to apply filters to multiple charts simultaneously.

Using a dashboard variable is a two part process. First you'll create the variable, then you'll connect it to one or more charts.

You can also use dashboard variables in Data Pipeline formulas. For an example, see our Knowledge Base article.

Create a Dropdown

Click Add Control from the dashboard sidebar menu and select Dropdown. You'll be redirected to the Chart Editor. Find the column you want to use for your category and drag it to the Dimensions field. The values in that column will populate a dropdown menu, which you can use to filter your charts.

Key-value pair dropdowns

You can drag a second Dimension into your categorical dropdown to display one value but filter by another. Check out our tutorial here.

  • Multiple values: check this checkbox to allow filtering by multiple values at once. If selecting multiple values, be sure your connected chart's filter is "is one of" and not "equals" or "like".

  • Empty state: When no values are selected in the dropdown, you can either Show all rows in the attached charts or Show none.

  • Default values: check this checkbox to set custom filter value(s) on load.

Connect a Dropdown to a chart

Open the chart you'd like to connect to your Dropdown in the Chart Editor. Drag the column you want to filter your dataset on into the Filters section, select the filter type, and select your variable name.

A couple things to keep in mind:

  • your Dropdown must have the same data type as the column you're filtering on
  • your filter type must match the type of Dropdown you're using. If you've left Multiple unchecked in the Dropdown's settings, make sure you don't use filter type 'is one of', for example.
 

Date Bucket

Create a Date Bucket

Click Add Control from the dashboard sidebar menu and select Date Bucket. A modal will pop up for you to customize your variable settings.

Rename the variable if desired, and select your default bucket.

Connect a Date Bucket to your chart

Once you've created your Date Bucket variable, open a chart you'd like to connect to your Date Bucket. You'll need a chart grouped by a date column.

Click the date Dimension to open its settings, and open the Time Bucket dropdown. Find your DATE_BUCKET variable and select it.

Note: Quarter bucketing is not supported by Google Analytics.

 

Hidden Variable

Create a Hidden Variable

Click Add Control from the dashboard sidebar menu and select Hidden Variable. A modal will pop up for you to customize your variable settings.

For Date Range or a single Date, you can either set a specific date, or choose a relative date such as the last 30 days.

To set a list of numbers or strings as your variable, check the Is List checkbox, and click Add Value for each additional list item you'd like to add. We'll translate this to a comma separated list. Text lists will be quoted, numerical lists will not.

Connect a Hidden Variable to your chart

Open the chart you'd like to connect to your Hidden Variable in the Chart Editor. Drag the column you want to filter your dataset on into the Filters section, select the filter type, and select your variable name. You'll need to ensure your Hidden Variable has the same data type as the column you're filtering on.

 

Text Input

Create a Text Input

Click Add Control from the dashboard sidebar menu and select Text Input. A modal will pop up for you to customize your variable settings.

Check Multi-value if you'd like to filter your charts by multiple values.

The Default Value will be used to filter your charts when no value is typed into the input box. This field is optional - leave it blank for an empty result set by default.

Partial string matching

The input variable requires an exact string match. To search by partial strings, select filter type "LIKE" in the Chart Creator, and use percent signs around your search string as needed. For example, to filter by names that start with "A", set your chart filter to filter by values 'like' your input variable, and enter A% into the input widget.

The input widget encloses the search value in single quotes, and therefore does not allow multiple input values at once.

For Default Start and Default End date, you can either set a specific date, or choose a relative date such as the last 30 days.

Connect a chart

Connect a Text Input to your chart

Open the chart you'd like to connect to your Text Input in the Chart Editor. Drag the column you want to filter your dataset on into the Filters section, select the filter type, and select your variable name. You'll need to ensure your Text Input has the same data type as the column you're filtering on. If you selected Multi-value when creating your text input, be sure to use filter type "is one of", not "equals".

 

Calendar

Create a Calendar

Click Add Control from the dashboard sidebar menu and select Calendar. A modal will pop up for you to choose your calendar variable settings.

For Data type, choose whether you want to filter your charts on a single Date or a Date Range.

Edit the Default value(s) using a custom date, or a relative date as needed.

Connect a Calendar to your chart

Find the chart you want to connect to your Calendar and open it in the Chart Editor. Drag the date column you want to filter by to the Filters section. Choose your filter type (you'll probably want to use between or between and including) from the first dropdown, then select {CALENDAR_NAME.START} and {CALENDAR_NAME.END} from the second and third dropdowns.

 

Date Slider

Create a Date Slider

Click Add Control from the dashboard sidebar menu and select Date Slider. You'll be redirected to the Chart Editor.

Connect a Date Slider to your chart

Find the chart you want to connect to your Date Slider and open it in the Chart Editor. Drag the date column you want to filter by to the Filters section. Choose your filter type (you'll probably want to use between or between and including) from the first dropdown, then select {DATE_SLIDER_NAME.START} and {DATE_SLIDER_NAME.END} from the second and third dropdowns.

If your Date Slider is grouped by hour or minute, select the between filter in the Date Slider Using Hour or Minute section.

 

Range Input

Create a Range Input

Click Add Control from the dashboard sidebar menu and select Range Input. A modal will pop up for you to customize your variable settings.

Choose the Min and Max for your range, edit Precision if you'd like to include decimal values, and set the Default values that will be selected when the dashboard is first loaded.

 

Connect a Range Input to your chart

Open the chart you'd like to connect to your Range Input in the Chart Editor. Drag the numeric column you want to filter your dataset on into the Filters section, select between and including for the filter type, and select your Range Input.

 

Connect a SQL Mode Chart

Chartio has a special syntax that will allow you to use your Dashboard Variables easily in SQL Mode.

Note: These instructions assume you have already added a Dashboard Variable.

Open your chart in SQL Mode. You should see a dropdown menu labeled Dashboard Variables. Open it, and select a variable from the list. That variable will be inserted into your query where the cursor is placed.

Syntax

Using the variables in SQL Mode is mostly straightforward, though the syntax for Dropdowns is slightly different than you may expect. See examples below. Note: syntax may vary depending on your database type. These examples are common use cases and do not cover all possible variable options.

Removing Quotes
Text variables are single-quoted. To remove the quotes, append .RAW to your variable:
{VARIABLE.RAW}
.RAW can only be used on single values, not lists.

Date Filters

(Calendar, Date Slider, Hidden Variable)

Date filters have two attributes: a start and end date.

SELECT SUM("m"."cost") AS "Total sum of Cost"
FROM "public"."marketing" AS "m"
WHERE "m"."created_date" BETWEEN {VARIABLE_NAME.START} AND     {VARIABLE_NAME.END};
Multiple select: IN

(Dropdown, Hidden Variable list)

Note: Column alias needs to be in single quotes.

SELECT COUNT(DISTINCT "a"."activity_id") AS "Count of Activity"
FROM "public"."activity" AS "a"
WHERE {VARIABLE_NAME.IN('"a"."activity_name"')};
Multiple select: NOT IN

(Dropdown, Hidden Variable list)

Note: Column alias needs to be in single quotes.

SELECT COUNT(DISTINCT "a"."activity_id") AS "Count of Activity"
FROM "public"."activity" AS "a"
WHERE {VARIABLE_NAME.NOT_IN('"a"."activity_name"')};
Single select: EQUALS

(Dropdown, Hidden Variable single value, Input Widget)

SELECT COUNT(DISTINCT "a"."activity_id") AS "Count of Activity"
FROM "public"."activity" AS "a"
WHERE "a"."activity_name" = {VARIABLE_NAME};

Advanced Examples

You can apply functions or CASE statements to columns you're filtering on.

CASE statement

(Dropdown, Hidden Variable list)

Statement inside bracket needs to be single quoted, and any other single quotes need to be escaped with a backslash.

SELECT COUNT(DISTINCT "a"."activity_id") AS "Count of Activity",
FROM "public"."activity" AS "a"
WHERE {DROPDOWN.IN('CASE "a"."activity_name" WHEN \'Comment\' THEN \'Post\' ELSE "a"."activity_name" END')};</pre>

Edit a variable

To edit an existing dashboard variable, open your dashboard settings (by selecting Settings from the dashboard's sidebar menu) and switch to the Variables tab.

Troubleshooting

If you are experiencing any formatting errors related to your dashboard variables, one of the best places to start troubleshooting is the query log. In the query log, you can view how Chartio translates your dashboard variable syntax into a SQL query, and how the query changes when specific values are selected.

For more information on accessing the query log, see our documentation here.

Relative Date Variables

Chartio offers built-in date variables that can be used across the application. They each resolve to a single date string, so you can use them anywhere you would otherwise enter a date value. For example, you can:

  • set default dates for dashboard date filters, such as a Calendar filter or a Date Slider
  • use in custom formulas in the Data Pipeline
  • use in SQL Mode
  • use in Interactive Mode filters

View examples

Available variables

  • {TODAY} resolves to the current date UTC - does not contain the current time.
  • {CURRENT_ISO_WEEK.START} and {CURRENT_ISO_WEEK.END}
  • {CURRENT_QUARTER.START} and {CURRENT_QUARTER.END}
  • {CURRENT_MONTH.START} and {CURRENT_MONTH.END}
  • {CURRENT_CALENDAR_YEAR.START} and {CURRENT_CALENDAR_YEAR.END}

Relative date variable functions

You can perform functions on date variables that will allow you to customize the date returned to fit your needs.

Multiple functions can be chained together; see examples below.

To try these out in Chartio, open the Explore tab in SQL Mode and add 'SELECT' before your date formula. For example:
SELECT {CURRENT_CALENDAR_YEAR.START.SUB(1, 'day')}

Units

Units are used to perform calculations on dates (add or subtract), and extract part of a date (for example, return only the year part of a date). They can be used in either singular or plural form, and include:

  • day
  • week
  • month
  • year
  • quarter (PART() only)

Add and subtract

Add/subtract from relative date variables to create other dates. Use the following format:

{DATE_VARIABLE.ADD(integer, 'units')}
{DATE_VARIABLE.SUB(integer, 'units')}

For example, to get the first day of the previous month:

{CURRENT_MONTH.START.SUB(1, 'month')}
Calculating the last day of the previous month

Let's say it's currently June. {CURRENT_MONTH.END.SUB(1, 'month')} will return 2016-05-30, because we're subtracting one month from 2016-06-30.

To get the last day of the previous month, use the following instead:

{CURRENT_MONTH.START.SUB(1, 'day')}

Extract part of a date

We have two functions for extracting part of a date: PART() and BUCKET(). PART() returns a number, and BUCKET() returns a date string.

PART()

PART() allows you to extract part of a date, and returns it as a number that can be used in calculations. Use the following format:

{DATE_VARIABLE.PART('units')}
PART() examples

Return the quarter number of the current day:

{TODAY.PART('quarter')}

Return the last day of the current month:

{CURRENT_MONTH.END.PART('day')}

Return the current year in number form:

{TODAY.PART('year')}
BUCKET()

BUCKET() allows you to extract part of a date, and returns it as a date string. Use the following format:

{DATE_VARIABLE.BUCKET('units')}
BUCKET() examples

Return the month and year of the previous month:

{CURRENT_MONTH.START.SUB(1, 'month').BUCKET('month')}

Return the ISO week date of the current day:

{TODAY.BUCKET('week')}

Return the quarter date of the current day:

{TODAY.BUCKET('quarter')}

Drilldown Variables

Chartio's Drilldown functionality allows you to click an area on a chart to update a Dashboard Variable value. The Dashboard Variable then updates any charts that are connected to it.

This is basically a two part process:

  1. Map your chart metrics (Drilldown Variables) to Dashboard Variables
  2. Filter your chart(s) with the Dashboard Variables

This is best explained with an example. I'll start by showing you what our final dashboard looks like, then I'll explain how to connect everything together.

Interacting with the Monthly Activity chart updates two dashboard variables: the Calendar Filter and the Categorical Dropdown. The Dashboard Variables then update the Daily Activity chart. This gives us a zoomed-in view of our Activity by Activity Type.

Let's walk through how to set this up. To begin with, we have two charts and two Dashboard Variables already on this Dashboard:

  1. Monthly Activity, a chart grouped by month and activity type
  2. Daily Activity, a chart grouped by day and activity type
  3. A Calendar Interval Dashboard Variable
  4. A Categorical Dropdown Dashboard Variable containing Activity values

Connecting your charts

First, we want to connect Monthly Activity to our two Dashboard Variables. This will allow our chart to update the Dashboard Variables whenever we click somewhere inside the Monthly Activity chart.

We'll click the menu icon on our Monthly Activity chart and select Edit Settings from the dropdown menu.

We'll then switch to the Drilldown tab in the Chart Settings modal. This tab allows you to map Dashboard Variables to Drilldown Variables (your chart metrics).

We want to link two Dashboard Variables to our chart: ACTIVITY (the Categorical Dropdown) and CALENDAR INTERVAL.

On our chart, Activity is the series (pivoted column) - so we'll choose series from the dropdown next to ACTIVITY. Our dates are on the x-axis, so we'll choose x-value from the dropdown for CALENDAR_INTERVAL.

Note: if your chart has x and y-axis labels, you'll see the those label names listed instead of 'x-value' and 'y-value'.

Link to another Dashboard

To link your Drilldown chart to another Dashboard, simply choose the dashboard from the Linked Dashboard dropdown in the Drilldown settings tab of your chart settings. You'll need to first add the necessary Dashboard Variables to the Linked Dashboard, so your chart can update them.

X-values, y-values, and series

Generally, x-values map to the x-axis, y-values to the y-axis, and series to the columns.

If you aren't sure you're selecting the correct chart metric to map to your Dashboard Variable, check the sample values listed below the Drilldown Variables dropdown to confirm.

If your chart has x and y-axis labels, those labels will be listed in the Drilldown Variables dropdown instead.

Connect the Dashboard Variables to your Chart(s)

We want our Daily Activity chart to update when the Dashboard Variables change, so we'll just need to filter our chart by our two Dashboard Variables.

On our Daily Activity chart, we'll click the menu icon and choose Edit Chart Data.

To connect our Activity Categorical Dropdown to the chart, we'll drag our Activity column to the Filters section. To connect our Calendar Interval, we'll drag Payment Date. Select the variable names from the filter options. Here's what the Chart Creator looks like with our filters added:

That's it! Your Drilldown Variables should work as expected.

Additional notes

  • For table charts with Drilldown variables, the selections are made per-row. If more than one Drilldown Variable is used in a table chart, clicking a row value will update all Dashboard variables for the row.
  • If you would like to use a line chart or scatterplot for your Drilldown, you may want to edit the Chart Settings to increase the line or dot size. This will allow for easier click interactions.

If you have any trouble setting up your Drilldown Variables, please feel free to send us an email at support@chartio.com.