Below is a cheatsheet of commonly used derived columns! Derived columns allow you to create formulas at the row-level to change or transform your data. You can find out more in this article .
| Title | Data Type | Expression | Description |
|---|---|---|---|
| Last Year | Date | IF( YEAR(date_field) = YEAR(NOW()) - 1, Metric, 0) |
Isolates data from the previous year. It evaluates each date record, returning the metric's actual value when the date is from last year, and 0 for all other years. This enables clean year-over-year comparisons by filtering out data from all periods except the previous year. |
| All but last year | Date | IF( YEAR(date_field) = YEAR(NOW()), 0, Metric) |
Excludes data from the current year. It evaluates each date record, returning 0 when the date is from the current year, and the metric's actual value for all other years. This enables historical analysis by filtering out current-year data while preserving values from previous periods. |
| Today | Date | IF( YEAR(date_field) = YEAR(NOW()), Metric, 0) |
Isolates data from the current year only. It evaluates each date record, returning the metric's actual value when the date is from the current year, and 0 for all other years. This enables focused analysis of present-year performance by filtering out historical data while highlighting only current-year values. |
| Everything until same date last year | Date | IF( DAY_OF_YEAR(date_field) <= DAY_OF_YEAR(NOW()), Metric, 0) |
Captures year-to-date data. It evaluates each date record, returning the metric's actual value for dates that fall within the same or earlier day of the year as today, and 0 for future days of the year. This enables year-to-date analysis by including only data from the portion of the year that has already elapsed, regardless of which year the record is from. |
| Net Promoter Score (NPS) | Hierarchy & Numeric | IF(NPS Metric <= 10 , IF(NPS Metric < 7 , 'Detractors' , IF(Last NPS score < 9 , 'Passives' , 'Promotors')), 'Unknown') |
Categorizes NPS (Net Promoter Score) responses into standard NPS segments. It first validates if the NPS Metric is within the valid range (≤10), then classifies scores below 7 as "Detractors," scores between 7–8 as "Passives," and scores of 9–10 as "Promoters." Any values outside the valid NPS range are labeled as "Unknown." This enables standard NPS analysis by converting numerical scores into their corresponding classification categories. Read more about Aggregation Formulas here. |
| Search for a string over multiple columns | Hierarchy | IF( STRPOS(Field_1, 'Keyword') > 0 OR STRPOS(Field_2, 'Keyword') > 0 OR STRPOS(Field_3, 'Keyword') > 0 ... , 'Contains_Keyword', 'Not_Contains_Keyword' ) |
Checks multiple fields for the presence of a specific keyword. It evaluates a series of text fields and returns "Contains_Keyword" if any field contains the target keyword, otherwise it returns "Not_Contains_Keyword." This enables binary classification of records based on keyword mentions across multiple data points. |
| Make coordinates with hierarchies | Geo | COORDS( CAST( latitude ,numeric), CAST( longitude ,numeric), 'wgs84' ) |
Allows you to create a coordinate point by first converting latitude and longitude into numeric data types and using the WGS84. It allows for visualization of geographic data (i.e. in map charts) |
| Create a date | Date & Hierarchy | CAST(DAY( Date_field), hierarchy) || '-' || CAST(MONTH(Date_field), hierarchy) || '-' || SUBSTR(CAST(YEAR( Date_field), hierarchy), 3, 4) |
Create a custom date format. In this case it will extract the day, month, and last two digits of the year from the Date field. Then it casts each component to a hierarchy type, and concatenates them with hyphens. The result is a string in the format "DD-MM-YY" |
| Convert numbers to weekdays | Date | IF( day_of_week = 1 , '1. Monday' , IF ( day_of_week = 2 , '2. Tuesday', IF ( day_of_week = 3 , '3. Wednesday' , IF ( day_of_week = 4 , '4. Thursday' , IF ( day_of_week = 5 , '5. Friday' , IF ( day_of_week = 6 , '6. Saturday' , IF ( day_of_week = 7 , '7. Sunday' , 'none' ))))))) |
Convert a field called day_of_the_week (assuming it exists within the dataset) to weekdays. |
| Empty rows and filled ones | Hierarchy | IF( Field_Name <> ' ' , '1' , '0' ) |
Creates a binary flag based on whether a text field contains any value. It returns "1" if the specified field contains any text (is not empty or just whitespace), and returns "0" if the field is empty or contains only whitespace. This enables simple filtering and counting based on field completion status. |
| Count 1 if there's a value and 0 for null values | Numeric | IF(COALESCE( field, 0) = 0, 0, 1) |
Creates a binary indicator that identifies records with non-zero values. It returns "1" when the field contains any non-zero value (handling nulls as zeros), and "0" when the field is either null or 0. |
| Value Threshold Flag | Hierarchy | IF(Field_Value >= Threshold, 'Yes', IF(Field_Value < Threshold, 'No', CAST(Field_Value, hierarchy))) |
Creates a binary indicator that identifies records with non-zero values. It returns "1" when the field contains any non-zero value (handling nulls as zeros), and "0" when the field is either null or 0. |
| Rolling Date | Date | DATE_ADD('day', DATE_DIFF('day',date_column, NOW())%7, date_column) |
This formula will group all data from the last 7 days into one point, data from the 7 days before that into another point, and so on. If you want to have a rolling period of a different amount of days, change out the number 7 in above formula to the amount you did like to compare on. Similarly, you can change the 'day' to another level ('week', 'month', ...). If you would like to implement Period Comparisions such as rolling dates, please refer to this article. |
| Comparable Period | Date | IF( DAY( date_column ) <= DAY( NOW() ), Measure, 0) |
This formula will return the Measure value if the day of the 'Date column' is less than or equal to the current day of the month, and 0 otherwise. |