Dashboard Example 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.

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 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

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

over

over(
    [ <PARTITION BY MyColumnName> ]  
    [ <ORDER BY MyColumnName> ]   
    [ <ROW or RANGE MyColumnName> ]  
    )     

Example:

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

round

round("MyColumnName",2) 

Parameter Values

to_char

to_char("MyColumnName", 'DateFormat')

Parameter Values

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')