Unlock the full potential of your data with Insights’ powerful workbook functions and formulas. Whether you're creating custom metrics, manipulating data, or leveraging AI-powered calculations, Insights provides the tools you need to tailor your analyses precisely to your requirements.
Table Calculations
Table calculations allow you to create ad-hoc metrics that are performed after query processing on the result set, similar to functionalities found in popular spreadsheet applications. Use these calculations to build contextual metrics such as:
Percent of Row or Column
Period Over Period Changes
Custom Calculations for Analyses or Visualizations
Workbook Functions
Insights supports a comprehensive range of workbook functions that you can use to create formulas for manipulating data and performing calculations on strings and numbers. Explore Insights' extensive list of available functions categorized below to find the right tools for your tasks.
Preset Quick-Add Functions
Quick-add functions provide ready-to-use calculations on top of numeric data in your result set. These functions are available for UI-generated result sets and 'SQL Super Powers' result sets and can be applied across pivots when active (e.g., row running total). Available quick calculations include:
% of Total
% of Total (Row)
% of Previous
% of Previous (Row)
% Change from Previous
% Change from Previous (Row)
Running Total
Running Total (Row)
Rank
Rank (Row)
How to Use Workbook Functions
Calculate with Workbook Functions
Navigate to the Calculation Tools:
In your workbook, click the "Add a column" icon or select the three-dot menu on the right side of a column header.
Ensure that the fields (dimensions or measures) you want to reference in your table calculation are included in the workbook query.
Create a New Calculation:
Start a new calculation from the function editor or directly in the cell.
Begin your formula with an
=sign.Wrap all alphabetic characters in double quotes (e.g.,
"Commentary"). Single quotes are not valid in Insights, similar to other spreadsheet applications.
Promote Table Calculations to the Workbook
Promote your calculations to dimensions or measures for reuse within the workbook. Note that some calculations may have limitations, such as those based on other calculations or those that include range references.
Calculate with Preset Quick-Add Functions
Access Quick Calculations:
In your workbook, click the three-dot menu on the right side of a column header.
Scroll to the bottom of the dropdown menu to find preset calculations ready to use with your existing workbook results.
Use Function Editor Icons:
Alternatively, access the same preset calculations through quick icons available in the function editor.
Calculate with AI
Leverage Insights' AI-powered calculations to create table calculations using natural language inputs. The AI Formula Helper is a fantastic tool that allows you to describe the calculation you need in plain language, and it will automatically identify and generate the appropriate formula or calculation for you. This means you don't need to know or look up specific functions; simply describe what you want, and Insights will handle the rest.
Metadata Sharing
Data Handling: Insights only transmits column IDs and their mappings to a table (e.g.,
{A: users.id, B: user.state, C: order.count}) to Calculations AI.
How to Use Calculations AI
Initiate AI Calculation:
In your workbook, click the "Add a column" icon or select the three-dot menu on the right side of a column header.
Ensure the necessary fields are included in the workbook query.
Create a Natural Language Formula:
Under the new table calculation column, select the initial cell to start your formula.
Choose "Natural Language Formula."
Enter your desired formula in plain text (e.g., "Multiply 'Order Total' with 'Order Count'").
View the Results:
Insights will generate the corresponding formula based on your natural language input.
Add Free-Text Values
Navigate to Free-Text Addition:
In your workbook, click the "Add a column" icon or select the three-dot menu on the right side of a column header.
Ensure the relevant fields are included in the workbook query.
Insert Free-Text Values:
Under the new calculation column, add your desired free-text values directly into the cells.
Leveraging Free-Text Values
Use free-text values to cross-reference columns or cells across other tabs. For example:
Create a New Column:
Enter an
=sign in the desired cell.
Reference Other Tabs:
Pull up the tab names or specific cells as needed.
Workbook-Style Functions
Calculation Building Blocks
Calculations in Insights are built using several primitive types, serving as the foundational elements for more complex formulas.
String Literals
Definition: Strings used as arguments in functions.
Example:
"Hello Insights"Note: Must be enclosed in double quotes.
Number Literals
Definition: Numeric values used in calculations.
Example:
123Note: Can be used as standalone values or as function arguments.
Logical Literals
Definition: Boolean values (
TRUEorFALSE).Example:
TRUENote: Must be in all caps to be recognized correctly.
Unary Operators / Negation
Definition: Operators that act on a single operand to negate its value.
Syntax:
-valueExample:
-orders_sum(returns the negative value of the daily budget)Note: Unary operators have only a right operand. The
+operator is also unary but is typically ignored since+1is equivalent to1.
Cell Reference
Definition: Notation pointing to a specific cell in a dataset.
Example:
C2Note:
C2references the third column “C” and the second row “2”.Column references can omit the row number (e.g.,
C) or use column names (e.g.,users.count).Calculations like
C1 + 10will automatically adjust for each row (e.g.,C2 + 10,C3 + 10, etc.).
Cell Range Reference
Definition: References a span between two cells, representing an array of data.
Example:
C1:C5Note: Inclusive of both start and end cells, covering
[C1, C2, C3, C4, C5].
Supported Functions
Insights includes predefined formulas to simplify calculations on values. Below is a comprehensive list of supported functions categorized by their type.
Math Functions
ABS
Description: Returns the absolute value of a number.
Syntax:
ABS(x)Example:
ABS(-14)Notes: The ABS function returns the absolute value, removing any negative sign.
AVERAGE
Description: Averages a list of numbers.
Syntax:
AVERAGE(list)Example:
AVERAGE(1, 2, 3, 4)Notes: Insights will apply the average function to all values in the specified list.
CEILING
Description: Rounds numbers up.
Syntax:
CEILING(x, <significance-optional>)Example:
CEILING(A1, 0.05)Notes: Optionally accepts a second argument to define the significance for rounding.
CORREL
Description: Returns the correlation coefficient of two cell ranges.
Syntax:
CORREL(array1, array2)Example:
CORREL(A1:A3, B1:B3)Notes: Calculates the correlation between two datasets.
COUNT
Description: Counts the number of cells that contain values in the column.
Syntax:
COUNT(x)Example:
COUNT(A1)Notes: The count applies to the entire column, not just from the specified cell.
COUNTA
Description: Counts the number of cells that are not empty.
Syntax:
COUNTA(x)Example:
COUNTA(A1)Notes: Counts all non-empty cells in the specified range.
COUNTIF
Description: Counts the number of cells that meet a given criteria.
Syntax:
COUNTIF(cell_range, criteria)Example:
COUNTIF(A2:A5, A1 >= 2)Notes:
COUNTIF() is not case sensitive.
Insights does not support string criteria.
COUNTIFS
Description: Counts the number of cells that meet multiple criteria.
Syntax:
COUNTIFS(cell_range1, criteria1, [cell_range2, criteria2], ...)Example:
COUNTIFS(A1:A5, A1 > 2, B3:B10, B1 > A1)Notes:
COUNTIFS() allows for multiple conditions.
COUNTIFS() is not case sensitive.
Insights does not support string criteria.
COVAR
Description: Returns covariance, the average of the products of deviations. Equivalent to COVAR.S.
Syntax:
COVAR(array1, array2)Example:
COVAR(A1:A10, B1:B10)Notes: Calculates the covariance between two datasets.
COVARIANCE.P
Description: Returns covariance, the average of the products of deviations of a population.
Syntax:
COVARIANCE.P(array1, array2)Example:
COVARIANCE.P(A1:A3, B1:B3)Notes: Calculates the population covariance between two datasets.
EXP
Description: Returns e raised to the power of a given number.
Syntax:
EXP(number)Example:
EXP(2)Notes: Computes the exponential value of the specified number.
FLOOR
Description: Rounds numbers down.
Syntax:
FLOOR(value, [significance-optional])Example:
FLOOR(A1, 0.05)Notes: Optionally accepts a second argument to define the significance for rounding.
INT
Description: Rounds the value down to the nearest integer. Alias for FLOOR.
Syntax:
FLOOR(value, [significance-optional])Example:
FLOOR(A1, 0.05)Notes: Functions identically to FLOOR.
INTERCEPT
Description: Returns the intercept of the linear regression line through data points in X and Y data points.
Syntax:
INTERCEPT(x_value, y_value)Example:
INTERCEPT(A1:A100, B1:B100)Notes: Any text of type string encountered in the value arguments will return null values.
LARGE
Description: Returns the nth largest value in a data set.
Syntax:
LARGE(array, k)Example:
LARGE(A1:A100, 4)Notes: Identifies the k-th largest value within the specified range.
LN
Description: Returns the natural logarithm of a number.
Syntax:
LN(number)Example:
LN(100)Notes: Calculates the natural logarithm (base e) of the specified number.
LOG
Description: Returns the logarithm of a number to the base you specify.
Syntax:
LOG(number, [base-optional])Example:
LOG(100, 10)Notes: The base argument is optional; if omitted, the default is 10.
LOG10
Description: Returns the base-10 logarithm of a number.
Syntax:
LOG10(number)Example:
LOG10(100)Notes: Specifically calculates the logarithm base 10.
MAX
Description: Returns the largest number in a set.
Syntax:
MAX(range)Example:
MAX(A1:A5)Notes: Accepts only a single column or range.
MEDIAN
Description: Returns the median number in a set.
Syntax:
MEDIAN(range)Example:
MEDIAN(B2:B8)Notes: Accepts only a single column or range.
MIN
Description: Returns the smallest number in a set.
Syntax:
MIN(range)Example:
MIN(C1:C10)Notes: Accepts only a single column or range.
MOD
Description: Returns the remainder from division.
Syntax:
MOD(dividend, divisor)Example:
MOD(10, 3)Notes: Calculates the remainder after dividing the dividend by the divisor.
MODE
Description: Returns the most common number in a set.
Syntax:
MODE(range)Example:
MODE(A1:A5)Notes: Accepts only a single column or range.
RANK
Description: Returns the rank of a number in a list of numbers.
Syntax:
RANK(number, ref, [direction])Example:
RANK(A1, A1:A10, 0)Notes: The direction argument is optional and defaults to descending if omitted.
ROUND
Description: Rounds a number to a specified number of digits.
Syntax:
ROUND(number, [num_digits])Example:
ROUND(100.1234, 2)Notes: The num_digits argument is optional and defaults to 0 if omitted.
ROUNDDOWN
Description: Rounds a number down, towards zero, to a specified number of digits.
Syntax:
ROUNDDOWN(number, [num_digits])Example:
ROUNDDOWN(100.1234, 2)Notes: The num_digits argument is optional and defaults to 0 if omitted.
ROUNDUP
Description: Rounds a number up, away from zero, to a specified number of digits.
Syntax:
ROUNDUP(number, [num_digits])Example:
ROUNDUP(100.1234, 2)Notes: The num_digits argument is optional and defaults to 0 if omitted.
SLOPE
Description: Returns the slope of the linear regression line through data points in Y and X data points.
Syntax:
SLOPE(y_values, x_values)Example:
SLOPE(B1:B100, A1:A100)Notes: Any text of type string encountered in the value arguments will return null values.
SMALL
Description: Returns the nth smallest value in a data set.
Syntax:
SMALL(array, k)Example:
SMALL(A1:A100, 4)Notes: Identifies the k-th smallest value within the specified range.
SQRT
Description: Returns a positive square root.
Syntax:
SQRT(number)Example:
SQRT(100)Notes: Calculates the square root of the specified number.
STDEV
Description: Estimates the standard deviation based on a sample of values.
Syntax:
STDEV(value)Example:
STDEV(A1)STDEV(A1:A5)
Notes:
STDEV function only accepts one argument.
If a value argument references text values, Insights will return the calculation as null.
STDEV.P
Description: Estimates the standard deviation based on an entire population.
Syntax:
STDEV.P(value)Example:
STDEV.P(A1)STDEV.P(A1:A5)
Notes:
STDEV.P function only accepts one argument.
If a value argument references text values, Insights will return the calculation as null.
SUM
Description: Adds all the numbers in a range of cells.
Syntax:
SUM(range)Example:
SUM(A1:A5)Notes: Calculates the total of all the numbers in the specified range.
SUMIF
Description: Adds the cells specified by a given criteria.
Syntax:
SUMIF(range, criteria, [sum_range-optional])Example:
SUMIF(B2:B8, B2 > 5, C2:C8)Notes:
The SUMIF function adds up values based on a specified condition.
Insights does not support string criteria.
SUMIFS
Description: Adds the cells specified by multiple criteria.
Syntax:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Example:
SUMIFS(C2:C8, B2:B8, B2 > 5)Notes:
The SUMIFS function adds up values based on multiple specified conditions.
Insights does not support string criteria.
SUMPRODUCT
Description: Returns the sum of the products of corresponding array components.
Syntax:
SUMPRODUCT(range1, range2, ...)Example:
SUMPRODUCT(A1:A5, B1:B5)Notes: Multiplies corresponding components in the given ranges and then sums those products.
TRUNC
Description: Truncates a number to an integer. Alias for FLOOR.
Syntax:
TRUNC(number)Example:
TRUNC(8.9)Notes: Removes the decimal portion of a number, leaving only the integer part.
VALUE
Description: Converts a text argument to a number.
Syntax:
VALUE(text)Example:
VALUE("123")Notes: Converts a text string that represents a number to an actual numeric value.
VAR
Description: Calculates the variance based on a sample of values.
Syntax:
VAR(value)Example:
VAR(1)VAR(A1)VAR(A2:A25)
Notes: VAR function only accepts one argument.
VAR.P
Description: Returns the variance of a population.
Syntax:
VAR.P(number/group/column)Example:
VAR.P(1)VAR.P(A1)VAR.P(A2:A25)
Notes: Calculates the population variance based on the specified values.
Trig Functions
ACOS
Description: Returns the arccosine of a number.
Syntax:
ACOS(number)Example:
ACOS(0.5)Notes: Returns the angle whose cosine is the specified number.
ATAN
Description: Returns the arctangent of a number.
Syntax:
ATAN(number)Example:
ATAN(0.5)Notes: Returns the angle whose tangent is the specified number.
COS
Description: Returns the cosine of a number.
Syntax:
COS(number)Example:
COS(45)Notes: Returns the cosine of an angle specified in radians.
COT
Description: Returns the cotangent of a number.
Syntax:
COT(number)Example:
COT(45)Notes: Returns the cotangent of an angle specified in radians.
DEGREES
Description: Converts radians to degrees.
Syntax:
DEGREES(radians)Example:
DEGREES(1.047)Notes: Converts radians to degrees.
Text Functions
CHAR
Description: Returns the character specified by a number.
Syntax:
CHAR(number)Example:
CHAR(10)Notes: Char Values follow the ASCII value mapping.
CONCAT or CONCATENATE
Description: Concatenates any number of strings.
Syntax:
CONCAT(string1, string2, ...)Example:
CONCAT("Hello", " ", "World")Notes: Combines multiple text strings into one.
CLEAN
Description: Returns text with the non-printable ASCII characters removed.
Syntax:
CLEAN(text)Example:
CONCAT("Hello"&CHAR(31))Notes: Removes non-printable ASCII characters; Unicode characters that aren't in ASCII are not removed.
FIND
Description: Returns the position of one string inside another.
Syntax:
FIND(find_text, within_text, [start_num])Example:
FIND("n", "Insights")Notes:
Insights FIND does not accept a third argument for “start number/index”.
LEFT
Description: Returns the specified number of characters from the start of a text string.
Syntax:
LEFT(text, [num_chars])Example:
LEFT("Hello", 3)Notes: Extracts characters from the beginning of a text string.
LEN
Description: Returns the length of a string.
Syntax:
LEN(text)Example:
LEN("Hello")Notes: Calculates the number of characters in a text string.
LOWER
Description: Converts text to lowercase.
Syntax:
LOWER(text)Example:
LOWER("Hello")Notes: Converts all letters in a text string to lowercase.
MID
Description: Returns a specific number of characters from a text string starting at the position you specify.
Syntax:
MID(text, start_num, num_chars)Example:
MID("Hello", 2, 3)Notes: Extracts a specific number of characters from a text string.
REPLACE
Description: Replaces characters within text.
Syntax:
REPLACE(old_text, start_num, num_chars, new_text)Example:
REPLACE("Hello", 2, 3, "i")Notes: Replaces characters in a text string with new text.
RIGHT
Description: Returns the specified number of characters from the end of a text string.
Syntax:
RIGHT(text, [num_chars])Example:
RIGHT("Hello", 3)Notes: Extracts characters from the end of a text string.
SEARCH
Description: Finds one text value within another (not case-sensitive).
Syntax:
SEARCH(find_text, within_text, [start_num])Example:
SEARCH("n", "Insights")Notes: Finds the position of one text string within another.
T
Description: Converts its arguments to text.
Syntax:
T(value)Example:
T(42)Notes: Converts a value to text.
TEXT
Description: Formats a value based on the format code.
Syntax:
TEXT(value, format_code)Example:
TEXT(1234, "$##,#")Notes: Formats a value according to the specified format code.
TRIM
Description: Removes all spaces from text except for single spaces between words.
Syntax:
TRIM(text)Example:
TRIM(" Hello World ")Notes: Removes extra spaces from a text string.
UPPER
Description: Converts text to uppercase.
Syntax:
UPPER(text)Example:
UPPER("Hello")Notes: Converts all letters in a text string to uppercase.
SUBSTITUTE
Description: Substitutes new_text for old_text in a text string.
Syntax:
SUBSTITUTE(text, old_text, new_text)Example:
SUBSTITUTE("Hello", "o", "i")Notes: Replaces occurrences of old text with new text.
Date & Time Functions
DATE
Description: Creates a date from a day, month, and year.
Syntax:
DATE(year, month, day)Example:
DATE(2022, 12, 22)Notes: Creates a date value from individual components.
DATEDIF
Description: Finds the difference between two dates.
Syntax:
DATEDIF(start_date, end_date, "unit")Example:
DATEDIF(A1, B1, "M")Notes: Calculates the difference between two dates in specified units.
DAY
Description: Returns the day of the month.
Syntax:
DAY(date)Example:
DAY("2022-12-22")Notes: Returns the day of the month from a date.
DAYS
Description: Finds the difference in days between two dates.
Syntax:
DAYS(start_date, end_date)Example:
DAYS(A1, B1)Notes: Calculates the difference in days between two dates.
HOUR
Description: Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Syntax:
HOUR(time)Example:
HOUR("15:30")Notes: Returns the hour portion of a time.
MINUTE
Description: Returns the minute as a number from 0 to 59.
Syntax:
MINUTE(time)Example:
MINUTE("15:30")Notes: Returns the minute portion of a time.
NOW
Description: Returns the current date and time as a date-time value.
Syntax:
NOW()Example:
NOW()Notes: Returns the current date and time.
SECOND
Description: Returns the second as a number from 0 to 59.
Syntax:
SECOND(time)Example:
SECOND("15:30:45")Notes: Returns the second portion of a time.
TODAY
Description: Returns the current date as a date value. Does not accept arguments.
Syntax:
TODAY()Example:
TODAY()Notes: Returns the current date.
WEEKDAY
Description: Returns the day of the week as a number from 1 to 7.
Syntax:
WEEKDAY(date, [type])Example:
WEEKDAY("2022-12-22", 2)Notes: Returns the day of the week for a given date.
WEEKNUM
Description: Returns the week number of a specific date.
Syntax:
WEEKNUM(date, [type])Example:
WEEKNUM("2022-12-22", 2)Notes: Returns the week number for a given date.
YEAR
Description: Returns the year corresponding to a date.
Syntax:
YEAR(date)Example:
YEAR("2022-12-22")Notes: Returns the year from a date.
Logic Functions
AND
Description: A logical AND, useful for IF statements and other logic operations. Returns TRUE if all of its operands are TRUE.
Syntax:
AND(condition1, condition2, ...)Example:
AND(A1 > 5, B1 < 10)Notes: Returns TRUE if all conditions are TRUE.
BITAND
Description: Bitwise AND operator.
Syntax:
BITAND(value1, value2)Example:
BITAND(6, 3)(Returns 2)Notes: Performs a bitwise AND operation.
BITOR
Description: Bitwise OR operator.
Syntax:
BITOR(value1, value2)Example:
BITOR(6, 3)(Returns 7)Notes: Performs a bitwise OR operation.
BITRSHIFT
Description: Bitwise right shift operator.
Syntax:
BITRSHIFT(value, shift_amount)Example:
BITRSHIFT(8, 2)(Returns 2)Notes: Performs a bitwise right shift operation.
BITXOR
Description: Bitwise XOR operator.
Syntax:
BITXOR(value1, value2)Example:
BITXOR(5, 3)(Returns 6)Notes: Performs a bitwise XOR operation.
IF
Description: An IF statement checks a condition, and if it’s TRUE, it returns the second argument; otherwise, it returns the third argument.
Syntax:
IF(condition, true_value, false_value)Example:
IF(A1 > 10, "big", "small")Notes: A conditional statement that returns values based on a specified condition.
IFNA
Description: Returns the value you specify if the formula returns null; otherwise returns the result of the formula.
Syntax:
IFNA(value, default_value)Example:
IFNA(A1/B1, "Division by zero error")Notes: Returns a specified value if the formula result is null.
IFS
Description: Similar to an IF statement but can have multiple conditions.
Syntax:
IFS(condition1, value1, condition2, value2, ..., [default_value])Example:
IFS(A1 > 90, "A", A1 > 80, "B", A1 > 70, "C", TRUE, "F")Notes: Handles multiple conditions and returns the corresponding result for the first true condition.
ISBLANK
Description: Returns TRUE if a value is blank.
Syntax:
ISBLANK(value)Example:
ISBLANK(A1)Notes: Checks if a value is blank.
ISNUMBER
Description: Returns TRUE if a value is a number.
Syntax:
ISNUMBER(value)Example:
ISNUMBER(10)Notes: Checks if a value is a number.
OR
Description: A logical OR, useful for IF statements and other logic operations. Returns TRUE if at least one of its operands is TRUE.
Syntax:
OR(condition1, condition2, ...)Example:
OR(A1 < 1, A1 > 3)Notes: Returns TRUE if at least one condition is TRUE.
Miscellaneous Functions
RAND
Description: Generates a random number between 0 and 1.
Syntax:
RAND()Example:
RAND()Notes: Generates a random number.
ROW
Description: Returns the current row number. Insights does not support arguments/references.
Syntax:
ROW()Example:
ROW()Notes: Returns the row number of the cell containing the function.
Position Functions
INDEX
Description: Returns a value or the reference to a value from an array.
Syntax:
INDEX(range, start_position)Example:
INDEX(A2:A25, 12)Notes:
If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively.
MATCH
Description: Returns the relative position of an item in a range that matches a specified value.
Syntax:
MATCH(search_arg, range)Example:
MATCH("Jane", A10:A25)Notes:
MATCH returns the relative position in an array or range of a matched value rather than the matched value itself.
PIVOT
Description: Returns a value from a specific pivot in the data table.
Syntax:
PIVOT(column)Example:
PIVOT("Sales")Notes:
🔥 Tips: Utilize PIVOT to extract specific aggregated values from your pivot tables efficiently.
PIVOTINDEX
Description: Returns the index of the current pivot.
Syntax:
PIVOTINDEX()Example:
PIVOTINDEX()Notes: Identifies the position of your current pivot within the data table.
PIVOTOFFSET
Description: Returns a pivot value offset from the current pivot column.
Syntax:
PIVOTOFFSET(column, offset)Example:
PIVOTOFFSET("Sales", -1)Notes:
🔥 Tips: Offers flexibility for moving averages or lists by allowing you to reference data relative to the current pivot.
SWITCH
Description: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
Syntax:
SWITCH(expression, value1, result1, [value2, result2], ...)Example:
SWITCH(A1, "apple", "fruit", "banana", "fruit", "carrot", "vegetable", "unknown")Notes:
Evaluates an expression against a list of values and returns the corresponding result.
The final pair is used as a default if no matches are found.
XLOOKUP
Description: Allows for cross-tab analysis by searching for a value in a specified range in another query tab and returning the corresponding value within another specified range.
Syntax:
XLOOKUP(lookup_value, <query_tab_name>!<lookup_range>, <query_tab_name>!<return_range>)Example:
XLOOKUP(C1, 'State Mottos'!A:A, 'State Mottos'!B:B)Notes:
For easy reference of columns, use the Copy XLookup Reference in the field's column header's menu options.
The XLOOKUP function can be performed on ranges in different query tabs within the same workbook.
Common Use Cases: Time series joins across tabs, fact lookups.
The lookup range references must reference a full, single column like
'State Mottos'!A:Aand not'State Mottos'!A:Cor'State Mottos'!A1:A4.
For more information, check out Combining Data Sets Across Different Topics in Insights
VLOOKUP
Description: Finds
lookup_valuein the first column oflookup_rangeand returns the corresponding value in the column specified bycolumn_numberin thelookup_range.Syntax:
VLOOKUP(lookup_value, lookup_range, column_number)Example:
VLOOKUP(C1, 'State Mottos'!A:A, 2)Notes: Searches for a value in the first column of a range and returns a value in the same row from a specified column.
Referencing Totals
Using row and column totals can be effective for more complex calculations across rows or columns independently. These aggregates can be referenced in calculations when they are activated for queries (using the table options menu).
Column Total:
${users.count:column_total}Row Total:
${users.count:row_total}Grand Total (columns and rows, lower right):
${users.count:grand_total}
These can also be chained with other functions, for example:
Percent of Row:
${users.count} * 100.0 / ${users.count:row_total}
Calculations can also be adjusted in the table options to rename or change formatting or decimals.
Internal Functions
There are some other functions you may see in the SQL that are not meant for end-user use but help make the SQL blocks easier to parse in normal usage.
OMNI_PIVOT_ROW(dimensions)
Structures the pivot table experience by setting the query columns outside the pivot.
OMNI_PIVOT(column_limit, pivots)
Structures the pivot table experience by setting the query columns to be pivoted, along with a limit on columns.
Example:
sqlCopy codeSELECT OMNI_PIVOT_ROW(users.sign_up_month), SUM(users.count) AS users_count FROM users GROUP BY users.sign_up_month
Table Calculation Examples
Percent of Cohort - Gross Retention
Often for these calculations, it's necessary to look across a given cohort/row and understand the percentage of the group that was retained. To perform calculations across pivots, use a given cell and the context of the row or column. Below are examples using ${users.count}, assuming data is grouped by ${users.sign_up[month]} and ${users.months_since_sign_up}.
Using OMNI_PIVOT_OFFSET():
sqlCopy code1.0 * ${users.count} / MAX(OMNI_OFFSET(${users.count}, 0, -100, 1, 200))This divides each count by the maximum of the row using a column offset of 100 columns back and 200 columns wide.
Using Row Totals for Simpler Calculations:
sqlCopy code1.0 * ${users.count} / ${users.count:row_total}
Note: Currently, calculations cannot be pinned to the first column, so a window function would be required for % of first value, but this feature will be available soon.
Get Started Today!
Enhance your data analysis with Insights’ versatile workbook functions and formulas. Whether you're crafting complex metrics, leveraging AI for intelligent calculations, or utilizing preset functions for quick insights, you have all the tools you need to drive your data analysis forward.
For additional assistance, reach out to our Advisory team via the chat-- we're happy to help!
This help desk article provides a comprehensive reference for using workbook functions and formulas within Insights. Keep this guide handy to streamline your data analysis and maximize your use of Insights’ powerful features.