Dashboard Example Expressions

Using common dashboard expressions

Description

Data in dashboards can be augmented with expressions. There are two primary types of expressions, metrics and calculated columns. Both can be written and modified with PostgreSQL-flavored SQL.

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

  1. Sign into plaidcloud.com and navigate to Dasboards
  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

Each dataset will contain a section for Metrics and a section for Calulated Columns.

Metrics

Metrics are expressions that are typically used to describe a field, including how it will be consolidated.

cast

cast("MyColumnName" AS datatype)    'Converts a value (of any type) into a specified datatype

Parameter Values

ParameterDescription
MyColumnNameThe value to convert
datatypeThe datatype to convert expression to

Example:

cast("MyColumnName" AS NUMERIC)

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

coalesce("BaselineCost",0.0)

divide, with a hack for avoiding DIV/0 errors

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

count

count(*)

distinct

distinct("MyColumnName")

first

first("MyColumnName")

min

min("MyColumnName")

max

max("MyColumnName")

nullif

nullif("MyColumnName","MyColumnName2")  'returns NULL if two expressions are equal, otherwise it returns the first expression.

Parameter Values

ParameterDescription
MyColumnNameExpression to be compared
MyColumnName2Expression to be compared

over

over(
    [ <PARTITION BY MyColumnName> ]  
    [ <ORDER BY MyColumnName> ]   
    [ <ROW or RANGE MyColumnName> ]  
    )     
ArgumentsDescription
PARTITION BYDivides the query result set into partitions
ORDER BYDefines the logical order of the rows within each partition of the result set
ROW/RANGELimits the rows within the partition by specifying start and end points within the partition

Example:

sum("MyColumnName") over (partition by "MyColumnName2")

round

round("MyColumnName",2) 

Parameter Values

ParameterDescription
NumberNumber to be rounded
DecimalsNumber of decimal places to round number to

to_char

to_char("MyColumnName", 'DateFormat')

Parameter Values

ParameterDescription
MyColumnNameA number or date that will be converted to a string
DateFormatFormat that will be used to convert value to a string

sum

sum("MyColumnName")

Conditional statement

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

## Calculated Columns
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')

Last modified December 20, 2022 at 9:27 AM EST: adding agent upgrade docs (44b6d60)