Create Heroku Dataclip widgets

Configurations for each visualization type available for our Heroku Dataclip data source.

Updated over a week ago

This guide walks you through the various options available when configuring Heroku Dataclip widgets.

Number widget

The widget expects numeric data for the column specified in the configuration under field.

Heroku number widget

From the configuration menu, specify the following options:

  • URL: The URL to your Dataclip, provided in the browser address field or from the share option.

  • Field: You can choose which options returned from the Dataclip you want to display in the number widget.
    The drop-down menu shows the attributes returned in the SELECT statement, in this example book_purchases and month, so we'll choose book_purchases. If the drop-down menu is empty, then your data is not being returned in the expected format.
    See the SQL example below for data that is returned in the correct way.

  • Display: Choose to display the first or last entry from the column indicated by field. e.g. in this example, the data is displayed in ascending order with the most recent month being last, so we choose last entry.

The example above uses the following SQL:

SELECT
date_trunc('month', purchases.created_at) as month,
sum(quantity * purchase_items.price) as book_purchases
FROM
products,
purchases,
purchase_items
WHERE
tags @> ARRAY[CAST('Book' as varchar(255))]
AND purchases.id = purchase_items.purchase_id
AND products.id = purchase_items.product_id
GROUP BY 1
ORDER BY 1 ASC;

This produces the following data:

month

book_purchases

2011-01-01 00:00:00+00

747.58

2011-02-01 00:00:00+00

953.48

2011-03-01 00:00:00+00

767.51

2011-04-01 00:00:00+00

1221.28

2011-05-01 00:00:00+00

931.50

2011-06-01 00:00:00+00

1045.41

2011-07-01 00:00:00+00

863.42

2011-08-01 00:00:00+00

629.56

2011-09-01 00:00:00+00

681.56

2011-10-01 00:00:00+00

827.51

2011-11-01 00:00:00+00

701.55

2011-12-01 00:00:00+00

771.57

Gauge widget

The current item field expects just numeric data from the column selected in field. Minimum and maximum values are automatically calculated based on all items in this column.

Data_Clip_Geck-o-meter_widget

From the configuration menu in the widget, specify the following options:

  • URL: The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
    โ€‹Field: You can choose which of the options returned from the Dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the SELECT statement, in this example book_purchases and month, so we'll choose book_purchases.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way.
    โ€‹Current Item: Specifies which data value in the field chosen to treat as the current item, the main white number in the picture above.

Column chart widget

The x-axis takes alpha-numeric data, and the y-axis takes just numeric data.

Data_Clip_Bar_Chart_widget

From the config menu in the widget, specify the following options:

  • URL: The URL to your Heroku Dataclip, provided in the browser address field or from the share option.
    โ€‹X-Axis Field: You can choose which of the options returned from the dataclip you want to display in the widget along the x-axis. The drop-down menu shows the attributes returned in the SELECT statement, in this example state and total_sales, so we'll choose state.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way.
    โ€‹Y-Axis Field: You can choose which of the options returned from the dataclip you want to display in the widget along the y-axis. The drop-down menu shows the attributes returned in the SELECT statement, in this example state and total_sales, so we'll choose total_sales.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way.

The example above uses the following SQL:

SELECT
details->'state' as state,
SUM(quantity * purchase_items.price) as total_sales
FROM
purchase_items,
purchases,
users
WHERE
users.id = purchases.user_id
AND purchases.id = purchase_items.purchase_id
AND details ? 'state'
GROUP BY 1
ORDER BY 1;

This produces the following data:

state

total_sales

Florida

10778.27

Illinois

7916.60

New York

6283.70

South Carolina

8769.53

Virginia

6566.08

RAG (Red, Amber, Green) widget

The widget expects numeric data for the column specified in the config under field. It displays up to 3 values, with null values being included in that count but not being shown.

Data_Clip_RAG_widget

From the configuration menu in the widget, specify the following options:

  • URL: The URL to your Heroku Dataclip, provided in the browser address field or from the share option.

  • Field: You can choose which of the options returned from the dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the SELECT statement, in this example gender and total_sales, so we'll choose total_sales.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way.

  • Display: Choose to display the first or last 3 entries from the column indicated by field. e.g. in this example, only three values are returned, so we'll choose first 3 entries.

  • State 1 label: Optional label shown against the first state, indicated in red in the above picture.

  • State 2 label: Optional label shown against the first state, indicated in amber in the above picture.

  • State 3 label: Optional label shown against the first state, indicated in green in the above picture.

  • Reverse colors: Show the colors in the reverse order, so GAR (Green, Amber, Red).

The example above uses the following SQL:

SELECT
coalesce(details->'sex', 'unknown') as gender,
SUM(quantity * purchase_items.price) as total_sales
FROM
purchase_items,
purchases,
users
WHERE
users.id = purchases.user_id
AND purchases.id = purchase_items.purchase_id
GROUP BY 1;

This produces the following data:

gender

total_sales

Unknown

105298.68

M

109714.74

F

104051.56

Funnel widget

The widget expects numeric data for the column specified in the config under field. It displays up to 8 values, with null values being included in that count but not being shown.

Data_Clip_funnel_Chart_widget

From the configuration menu in the widget, specify the following options:

  • URL: The URL to your Heroku Dataclip, provided in the browser address field or from the share option.

  • Field: You can choose which of the options returned from the dataclip you want to display in the widget. The drop-down menu shows the attributes returned in the SELECT statement, in this example gender and total_sales, so we'll choose total_sales.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example below for data that is returned in the correct way.

  • Display: Choose to display the first or last 8 entries from the column indicated by 'field'. e.g. in this example, only three values are returned so we'll choose first 8 entries.

  • Funnel 1 label: Optional label shown against the first funnel. Unknown in our example.

  • Funnel 2 label: Optional label shown against the first state, indicated in amber in the above picture.

  • Funnel 3 label: Optional label shown against the second funnel. Male in our example

  • Funnel 4 label: Optional label shown against the fourth funnel.

  • Funnel 5 label: Optional label shown against the fifth funnel.

  • Funnel 6 label: Optional label shown against the sixth funnel.

  • Funnel 7 label: Optional label shown against the seventh funnel.

  • Funnel 8 label: Optional label shown against the eighth funnel.

  • Reverse colors: Show the colors in the reverse order, so larger numbers are red instead of green, and smaller ones are green instead of red.

  • Hide percentages: Percentages are shown as conversions between stages in the funnel. In our example we aren't displaying data as stages so we'd set the Hide percentages to Yes.

Line chart widget

The x-axis takes alpha-numeric data, and the y-axis takes just numeric data. Date/time values used in the x-axis are automatically detected and are automatically formatted for you in the line chart. Non-date/time data can also be used but isn't re-formatted in any way.

Data_Clip_Line_Chart_widget

From the configuration menu in the widget, specify the following options:

  • URL: The URL to your Heroku Dataclip, provided in the browser address field or from the share option.

  • X-Axis Field: You can choose which of the options returned from the dataclip you want to display in the widget along the x-axis. The drop-down menu shows the attributes returned in the SELECT statement, in this example book_purchases and month, so we'll choose month.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way.

  • Y-Axis Field: You can choose which of the options returned from the dataclip you want to display in the widget along the y-axis. The drop-down menu shows the attributes returned in the SELECT statement, in this example book_purchases and month, so we'll choose book_purchases.
    If the drop-down menu is empty, then your data is not being returned in the expected format. See the SQL example above for data that is returned in the correct way.

Did this answer your question?