Dashboards

• 1: Learning About Dashboards
• 2: Using Dashboards
• 3: Formatting Numbers and Other Data Types
• 4: Example Calculated Columns
• 5: Example Metrics

1 - Learning About Dashboards

Description

Dashboards support a wide range of use cases from static reporting to dynamic analysis. Dashboards support complex reporting needs while also providing an intuitive point-and-click interface. There may be times when you run into trouble. A member of the PlaidCloud Support Team is always available to assist you, but we have also compiled some tips below in case you run into a similar problem.

Common Questions and Answers for Dashboard

Preferred Browser

Due to frequent caching, Google Chrome is usually the best web browser to use with Dashboard. If you are using another browser and encounter a problem, we suggest first clearing the cache and cookies to see if that resolves the issue. If not, then we suggest switching to Google Chrome and seeing if the problem recurs.

Sync Delay

• Problem: After unpublishing and publishing tables in the Dashboards area, the data does not appear to be syncing properly.
• Solutions: Refresh the dashboard. Currently, old table data is cached, so it is necessary to refresh the dashboard when rebuilding tables.

Table Sync Error

• Problem: After recreating a table using the same published name as a previous table, the table is not syncing, even after hitting refresh on the dashboard, publishing, unpublishing, and republishing the table.
• Solutions: Republish the table with a different name. The Dashboard data model does not allow for duplicate tables, or tables with the same published name and project ID.

Cache Warning

• Problem: A warning popped up on the upper right saying “Loaded data cached 3 hours ago. Click to force-refresh.”
• Solutions: Click on the warning to force-refresh the cache. You can also click the drop-down menu beside “Edit dashboard” and select “Force refresh dashboard” there. Either of these options will refresh within the system and is preferred to refreshing the web browser itself.

Permission Warning

• Problem: My published dashboard is populating with the same error in each section where data should be populated: “This endpoint requires the datasource… permission”

• Solutions: Check that the datasources are not old. Most likely, the charts are pulling from outdated material. If this happens, update the charts with new datasources.

• Problem: I am getting the same permission warning from above, but my colleague can view the chart data.

• Solutions: If the problem is that one individual can see the data in the charts and another cannot, the second person may need to be granted permission by someone within the permitted category. To do so:

  1. Go to Charts
  2. Select the second small icon of a pencil and paper next to the chart you want to grant access to
  3. Click Edit Table
  4. Click Detail
  5. Click Owners and add the name of the person you want to grant access to and save.

Saving Modified Filters to Dashboard

• Problem: I modified filters in my draft model and want to save them to my dashboard. The filters are not in the list. In my draft model, a warning stated, “There is no chart definition associated with this component, could it have been deleted? Delete this container and save to remove this message.”
• Solutions: Go to “Edit Chart.” From there, make sure the “Dashboards” section has the correct dashboard filled in. If it is blank, add the correct dashboard name.

Formatting Numbers: Breaks

• Problem: My number formatting is broken and out of order.
• Solutions: The most likely reason for this break is the use of nulls in a numeric column. Using a filter, eliminate all null numeric columns. Try running it again. If that does not work, review the material provided here: http://bl.ocks.org/zanarmstrong/05c1e95bf7aa16c4768e or here: https://github.com/apache-superset/superset-ui/issues. Finally, always feel free to reach out to a PlaidCloud Support team member. This problem is known, and a more permanent solution is being developed.

Formatting Numbers

To round numbers to nearest integer:

1. Do not use: ,.0f
2. Instead use: ,d or $,d for dollars

Importing Existing Dashboard

• Problem: I’m importing an existing dashboard and getting an error on my export.
• Solutions: First, check whether the dashboard has a “Slug.” To do this, open Edit Dashboard, and the second section is titled Slug. If that section is empty or says “null,” then this is not the problem. Otherwise, if there is any other value in that field, you need to ensure that export JSON has a unique slug value. Change the slug to something unique.

2 - Using Dashboards

Description

Usually, members will have access to multiple workspaces and projects. Having this data in multiple spots, however, may not always be desirable. This is why PlaidCloud allows the ability to view all of the accessible data in a single location through the use of dashboards and highly intuitive data exploration. PlaidCloud Dashboards (where the dashboards and data exploration are integrated) provides a rich pallet of visualization and data exploration tools that can operate on virtually any size dataset. This setup also makes it possible to create dashboards and other visualizations that combine information across projects and workspaces, including Ad-hoc analysis.

Editing a Table

The message you receive after creating a new table also directs you to edit the table configuration. While there are more advanced features to edit the configuration, we will start with a limited and more simple portion. To edit table configuration:

1. Click on the edit icon of the desired table
2. Click the “List Columns” tab
3. Arrange the columns as desired
4. Click “Save”

This allows you to define the way you want to use specific columns of your table when exploring your data.

• Groupable: If you want users to group metrics by a specific field
• Filterable: If you need to filter on a specific field
• Count Distinct: If you want want to get the distinct count of this field
• Sum: If this is a metric you want to sum
• Min: If this is a metric you want to gather basic summary statistics for
• Max: If this is a metric you want to gather basic summary statistics for
• Is temporal: This should be checked for any date or time fields

Exploring Your Data

To start exploring your data, simply click on the desired table. By default, you’ll be presented with a Table View.

Getting a Data Count

To get a the count of all your records in the table:

1. Change the filter to “Since”

2. Enter the desired since filter

   • You can use simple phrases such as “3 years ago”

3. Enter the desired until filter

   • The upper limit for time defaults is “now”

4. Select the “Group By” header

5. Type “Count” into the metrics section

6. Select “COUNT(*)”

7. Click the “Query” button

You should then see your results in the table.

If you want to find the count of a specific field or restriction:

1. Type in the desired restriction(s) in the “Group By” field
2. Run the query

Restricting Result Number

If you only need a certain number of results, such as the top 10:

1. Select “Options”
2. Type in the desired max result count in the “Row Limit” section
3. Click “Query”

Additional Visualization Tools

To expand abbreviated values to their full length:

1. Select “Edit Table Config”
2. Click “List Sql Metric”
3. Click “Edit Metric”
4. Click “D3Format”

To edit the unit of measurement:

1. Select “Edit Table Config”
2. Click “List Sql Metric”
3. Click “Edit Metric”
4. Click “SQL Expression”

To change the chart type:

1. Scroll to “Chart Options”
2. Fill in the required fields
3. Click “Query”

From here you are able to set axis labels, margins, ticks, etc.

3 - Formatting Numbers and Other Data Types

Formatting numbers and other data types

There are 2 ways of formatting numbers in PlaidCloud. One way is to transform the values in the tables directly, and a second (more common way) is to format them on display so the values don't lose precision in the table and the user can see the values in a cleaner, more appropriate way.

When I display a value on a dashboard, how do I format it the way I want? The core way to display a value is through a chart object on a dashboard. Charts can be Tables, Big Numbers, Bar Charts, and so on. Each chart object may have a slightly different place or means to display the values. For example, in Tables, you can change the format for each column, and for a Big Number, you can change the format of the number.

To change the format, edit the chart and locate the D3 FORMAT or NUMBER FORMAT field. For a Big Number chart, click on the CUSTOMIZE tab, and you will see NUMBER FORMAT. For a Table, click on the CUSTOMIZE tab, select a number column (displayed with a #) in CUSTOMIZE COLUMN and you will see the D3 FORMAT field.

The default value is Adaptive formatting. This will adjust the format based on the values. But if you want to fix it to a format (i.e. $12.23 or 12,345,678), then you select the format you want from the dropdown or manually type a different value (if the field allows).

D3 Formatting - what is it?

D3 Formatting is a structured, formalized means to display data results in a particular format. For example, in certain situations you may wish to display a large value as 3B (3 billion), formatted as .3s in D3 format, or as 3,001,238,383, formatted as ,d. Another common example is the decision to represent dollar values with 2 decimal precision, or to round that to the nearest dollar $,d or $,.2f to show dollar sign, commas, 2 decimal precision, and a fixed point notation. For a deeper dive into D3, see the following site: GitHub D3

General D3 Format

The general structure of D3 is the following:

[​[fill]align][sign][symbol][0][width][,][.precision][~][type]

The fill can be any character (like a period, x or anything else). If you have a fill character, you then have an align character following it, which must be one of the following:

> - Right-aligned within the available space. (Default behavior). < - Left-aligned within the available space. ^ - Centered within the available space. = - like >, but with any sign and symbol to the left of any padding.

The sign can be: - - blank for zero or positive and a minus sign for negative. (Default behavior.) + - a plus sign for zero or positive and a minus sign for negative. ( - nothing for zero or positive and parentheses for negative. (space) - a space for zero or positive and a minus sign for negative.

The symbol can be: $ - apply currency symbol.

The zero (0) option enables zero-padding; this implicitly sets fill to 0 and align to =.

The width defines the minimum field width; if not specified, then the width will be determined by the content. For example, if you have 8, the width of the field will be 8 characters.

The comma (,) option enables the use commas as separators (i.e. for thousands).

Depending on the type, the precision can either indicate the number of digits that follow the decimal point (types f and %), or the number of significant digits (types ​, g, r, s and p). If the precision is not specified, it defaults to 6 for all types except (none), which defaults to 12.

The tilde ~ option trims insignificant trailing zeros across all format types. This is most commonly used in conjunction with types r, s and %.

types

Examples

4 - Example Calculated Columns

Description

Data in dashboards can be augmented with calculated columns. Each dataset will contain a section for calculated columns. Calculated columns can be written and modified with PostgreSQL-flavored SQL.

Navigating to a dataset

In order to view and edit metrics and calculated expressions, perform the following steps:

1. Sign into plaidcloud.com and navigate to dashboards
2. From within visualize.plaidcloud.com, navigate to Data > Datasets
3. Search for a dataset to view or modify
4. Modify the dataset by hovering over the edit button beneath Actions

Examples

count

COUNT(*)

min

min("MyColumnName")

max

max("MyColumnName")

coalesce (useful for converting nulls to 0.0, for instance)

coalesce("BaselineCost",0.0)

divide

divide, with a hack for avoiding DIV/0 errors

sum("so_infull")/(count(*)+0.00001)

conditional statement

CASE WHEN "Field_A"= 'Foo' THEN max(coalesce("Value_A",0.0)) - max(coalesce("Value_B",0.0)) END

5 - Example Metrics

Description

Data in dashboards can be augmented with metrics. Each dataset will contain a section for Metrics. Metrics can be written and modified with PostgreSQL-flavored SQL.

Navigating to a dataset

In order to view and edit metrics and calculated expressions, perform the following steps:

1. Sign into plaidcloud.com and navigate to dashboards
2. From within visualize.plaidcloud.com, navigate to Data > Datasets
3. Search for a dataset to view or modify
4. Modify the dataset by hovering over the edit button beneath Actions

Examples

Calculated columns are typically additional columns made by combining logic and existing columns.

convert a date to text

to_char("week_ending_sol_del_req", 'YYYY-mm-dd')

various SUM examples

SUM(Value)

SUM(-1*"value_usd_mkp") / (0.0001+SUM(-1*"value_usd_base"))

(SUM("Value_USD_VAT")/SUM("Value_USD_HEADER"))*100

sum(delivery_cases) where Material_Type = Gloves

sum("total_cost") / sum("delivery_count")

various case examples

CASE WHEN
SUM("distance_dc_xd") = 0 THEN 0
ELSE
sum("XD")/sum("distance_dc_xd")
END

sum(CASE
WHEN "FUNCTION" = 'OM' THEN "VALUE__FC"
ELSE 0.0
END)

count

count(*)

First and Cast

public.first(cast("PRETAX_SEQ" AS NUMERIC))

Round

round(Sum("GROSS PROFIT"),0)

Concat

concat("GCOA","CC Code")