Expressions

Expressions

The expression/formula language in Insights is based on python syntax. The underlying library used for query building is ibis, you can check the documentation here to know more.

Following are the list of helper functions that make it easier to write expressions in Insights:

Aggregate Functions

1. Count

   • Syntax: count(column)
   • Description: Returns the count of non-null values in the column.
   • Example: count(sales)
   • Returns: 100

2. Count Distinct

   • Syntax: distinct_count(column)
   • Description: Returns the count of distinct values in the column.
   • Example: distinct_count(customer_id)
   • Returns: 50

3. Sum

   • Syntax: sum(column)
   • Description: Returns the sum of values in the column.
   • Example: sum(sales)
   • Returns: 10000

4. Average

   • Syntax: avg(column)
   • Description: Returns the average of values in the column.
   • Example: avg(sales)
   • Returns: 100

5. Minimum

   • Syntax: min(column)
   • Description: Returns the minimum value in the column.
   • Example: min(sales)
   • Returns: 50

6. Maximum

   • Syntax: max(column)
   • Description: Returns the maximum value in the column.
   • Example: max(sales)
   • Returns: 200

7. Group Concat

   • Syntax: group_concat(column)
   • Description: Concatenates the values in the column.
   • Example: group_concat(product_name)
   • Returns: "Product A, Product B, Product C"

8. Sum if

   • Syntax: sum_if(condition, column)
   • Description: Returns the sum of values in the column that satisfy the condition.
   • Example: sum_if(sales > 1000, sales)
   • Returns: 5000

9. Count if

   • Syntax: count_if(condition, column)
   • Description: Returns the count of values in the column that satisfy the condition.
   • Example: count_if(sales > 1000, sales)
   • Returns: 5

10. Distinct Count if

    • Syntax: distinct_count_if(condition, column)
    • Description: Returns the count of distinct values in the column that satisfy the condition.
    • Example: distinct_count_if(sales > 1000, customer_id)
    • Returns: 3

Conditional Functions

1. If Else

   • Syntax: if_else(condition, true_value, false_value)
   • Description: Returns the truevalue if the condition is true, else returns the falsevalue.
   • Example: if_else(sales > 1000, "High", "Low")
   • Returns: "High"

2. Case

   • Syntax: case(condition1, value1, condition2, value2, ..., default_value)
   • Description: Returns the value corresponding to the first true condition. If no condition is true, returns the default_value.
   • Example: case(sales > 1000, "High", sales > 500, "Medium", "Low")
   • Returns: "High"

3. Coalesce

   • Syntax: coalesce(column1, column2, ..., default_value)
   • Description: Returns the first non-null value from the columns. If all columns are null, returns the default_value.
   • Example: coalesce(product_name, product_code, "Unknown")
   • Returns: "Product A"

String Functions

1. Lower

   • Syntax: lower(column)
   • Description: Converts the column values to lowercase.
   • Example: lower(product_name)
   • Returns: "product a"

2. Upper

   • Syntax: upper(column)
   • Description: Converts the column values to uppercase.
   • Example: upper(product_name)
   • Returns: "PRODUCT A"

3. Concat

   • Syntax: concat(column1, column2, ..., separator)
   • Description: Concatenates the columns with the separator.
   • Example: concat(first_name, last_name, " ")
   • Returns: "John Doe"

4. Replace

   • Syntax: replace(column, old_value, new_value)
   • Description: Replaces the oldvalue with the newvalue in the column.
   • Example: replace(product_name, "A", "B")
   • Returns: "Product B"

5. Substring

   • Syntax: substring(column, start, length)
   • Description: Returns a substring of the column starting from the start index with the specified length.
   • Example: substring(product_name, 0, 7)
   • Returns: "Product"

6. Contains

   • Syntax: contains(column, substring)
   • Description: Returns true if the column contains the substring, else false.
   • Example: contains(product_name, "Product")
   • Returns: True

7. Not Contains

   • Syntax: not_contains(column, substring)
   • Description: Returns true if the column does not contain the substring, else false.
   • Example: not_contains(product_name, "A")
   • Returns: False

8. Starts With

   • Syntax: starts_with(column, prefix)
   • Description: Returns true if the column starts with the prefix, else false.
   • Example: starts_with(product_name, "Product")
   • Returns: True

9. Ends With

   • Syntax: ends_with(column, suffix)
   • Description: Returns true if the column ends with the suffix, else false.
   • Example: ends_with(product_name, "A")
   • Returns: True

10. Length

    • Syntax: length(column)
    • Description: Returns the length of the column.
    • Example: length(product_name)
    • Returns: 10

Numeric Functions

1. Abs

   • Syntax: abs(column)
   • Description: Returns the absolute value of the column.
   • Example: abs(sales)
   • Returns: 100

2. Round

   • Syntax: round(column, decimals)
   • Description: Rounds the column to the specified number of decimals.
   • Example: round(sales, 2)
   • Returns: 100.50

3. Floor

   • Syntax: floor(column)
   • Description: Returns the largest integer less than or equal to the column.
   • Example: floor(sales)
   • Returns: 100

4. Ceil

   • Syntax: ceil(column)
   • Description: Returns the smallest integer greater than or equal to the column.
   • Example: ceil(sales)
   • Returns: 101

Date Functions

1. Year

   • Syntax: year(column)
   • Description: Returns the year part of the date column.
   • Example: year(order_date)
   • Returns: 2022

2. Quarter

   • Syntax: quarter(column)
   • Description: Returns the quarter part of the date column.
   • Example: quarter(order_date)
   • Returns: 3

3. Month

   • Syntax: month(column)
   • Description: Returns the month part of the date column.
   • Example: month(order_date)
   • Returns: 10

4. Week of Year

   • Syntax: week_of_year(column)
   • Description: Returns the week of the year of the date column.
   • Example: week_of_year(order_date)
   • Returns: 41

5. Day of Year

   • Syntax: day_of_year(column)
   • Description: Returns the day of the year of the date column.
   • Example: day_of_year(order_date)
   • Returns: 288

6. Day of Week

   • Syntax: day_of_week(column)
   • Description: Returns the day of the week (0-6) of the date column.
   • Example: day_of_week(order_date)
   • Returns: 4

7. Day

   • Syntax: day(column)
   • Description: Returns the day part of the date column.
   • Example: day(order_date)
   • Returns: 15

8. Hour

   • Syntax: hour(column)
   • Description: Returns the hour part of the date column.
   • Example: hour(order_date)
   • Returns: 10

9. Minute

   • Syntax: minute(column)
   • Description: Returns the minute part of the date column.
   • Example: minute(order_date)
   • Returns: 30

10. Second

    • Syntax: second(column)
    • Description: Returns the second part of the date column.
    • Example: second(order_date)
    • Returns: 45

11. Microsecond

    • Syntax: microsecond(column)
    • Description: Returns the microsecond part of the date column.
    • Example: microsecond(order_date)
    • Returns: 500000

12. Date Diff

    • Syntax: date_diff(column1, column2, unit)
    • Description: Returns the difference between two date columns in the specified unit. Units can be "year", "quarter", "month", "week", "day", "hour", "minute", "second", "millisecond", "microsecond", "nanosecond".
    • Example: date_diff(order_date, delivery_date, "day")
    • Returns: 5

13. Format Date

    • Syntax: format_date(column, format)
    • Description: Formats the date column according to the specified format. The format should be a valid ANSI strftime format.
    • Example: format_date(order_date, "%Y-%m-%d")
    • Returns: "2022-10-15"

14. Now

    • Syntax: now()
    • Description: Returns the current date and time.
    • Example: now()
    • Returns: "2022-10-15 10:30:00"

15. Today

    • Syntax: today()
    • Description: Returns the current date.
    • Example: today()
    • Returns: "2022-10-15"

Boolean Functions

1. Is in

   • Syntax: is_in(column, value1, value2, ...)
   • Description: Returns true if the column value is in the list of values, else false.
   • Example: is_in(product_name, "Product A", "Product B")
   • Returns: True

2. Is Not in

   • Syntax: is_not_in(column, value1, value2, ...)
   • Description: Returns true if the column value is not in the list of values, else false.
   • Example: is_not_in(product_name, "Product A", "Product B")
   • Returns: False

3. Is Set

   • Syntax: is_set(column)
   • Description: Returns true if the column value is not null, else false.
   • Example: is_set(product_name)
   • Returns: True

4. Is Not Set

   • Syntax: is_not_set(column)
   • Description: Returns true if the column value is null, else false.
   • Example: is_not_set(product_name)
   • Returns: False

5. Is Between

   • Syntax: is_between(column, start, end)
   • Description: Returns true if the column value is between the start and end values, else false.
   • Example: is_between(sales, 1000, 5000)
   • Returns: True

6. Is Not Between

   • Syntax: is_not_between(column, start, end)
   • Description: Returns true if the column value is not between the start and end values, else false.
   • Example: is_not_between(sales, 1000, 5000)
   • Returns: False

Utility Functions

1. USD to INR

   • Syntax: to_inr(currency_column, amount_column, rate=83)
   • Description: Converts the values in the amount column from USD to INR based on the rate. If the currency is not USD, the amount is returned as is. Default rate is 83.
   • Example: to_inr(currency, amount, 83)
   • Returns: 8300

2. INR to USD

   • Syntax: to_usd(currency_column, amount_column, rate=83)
   • Description: Converts the values in the amount column from INR to USD based on the rate. If the currency is not INR, the amount is returned as is. Default rate is 83.
   • Example: to_usd(currency, amount, 83)
   • Returns: 100

3. Row Number

   • Syntax: row_number()
   • Description: Returns the row number of the current row.
   • Example: row_number()
   • Returns: 1

4. Previous Period Value

   • Syntax: previous_period_value(column, date_column, offset=1)
   • Description: Returns the value of the column for the previous period based on the date column. Offset specifies the number of periods to go back.
   • Example: previous_period_value(sales, order_date, 1)
   • Returns: 1000

5. Next Period Value

   • Syntax: next_period_value(column, date_column, offset=1)
   • Description: Returns the value of the column for the next period based on the date column. Offset specifies the number of periods to go forward.
   • Example: next_period_value(sales, order_date, 1)
   • Returns: 2000

6. Percentage Change

   • Syntax: percentage_change(column, date_column, offset=1)
   • Description: Returns the percentage change in the value of the column compared to the previous period based on the date column. Offset specifies the number of periods to go back.
   • Example: percentage_change(sales, order_date, 1)
   • Returns: 10

7. Is First Row

   • Syntax: is_first_row(group_by, order_by, sort_order="asc")
   • Description: Returns 1 if the current row is the first row in the group, else 0. Sort order can be "asc" or "desc".
   • Example: is_first_row(customer_id, order_date, "asc")
   • Returns: 1

8. Create Buckets

   • Syntax: create_buckets(column, num_buckets)
   • Description: Creates buckets based on the values in the column. The number of buckets specifies the number of buckets to create.
   • Example: create_buckets(sales, 5)
   • Returns: "1000-2000"