Example Calculated Columns

Examples of calculated column expressions

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:

Sign into plaidcloud.com and navigate to dashboards
From within visualize.plaidcloud.com, navigate to Data > Datasets
Search for a dataset to view or modify
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)

substring

substring("PERIOD",6,2)

cast

CAST("YEAR" AS integer)-1

concat

concat("Biller Entity" , ' ', "Country_biller")

to_char

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

left

left("period",4)

divide

divide, with a hack for avoiding DIV/0 errors

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

Note: A better way to do this would be to check for a null or zero denominator and then coalese to zero rather than attempting the division.

conditional statement

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

CASE WHEN "sol_otif_pod_missing" = 1 THEN
'POD is missing.'
ELSE
'POD exists.'
END

case when "Customer DC" = "origin_dc" or "order_reason_type" = 'Off Schedule' or "mot_type" = 'UPS' then
    'Yes'
else
    'No'
end

CASE WHEN "module_type" is NULL THEN '---' ELSE "module_type" END

CASE WHEN "NODE_TYPE" = 'External' THEN '3rd Party' ELSE "ENTITY_LOCATION_DESCRIPTION" END

concatenate

concat("Class",' > ',"Product Family",' > ',"Meta Series")