Below is the list of CubeWeaver built-in functions. Check docs if you need custom functions.

abs(n)

  • Returns the absolute value of n
  • Example: abs([B:3])

accrint(issue, firstInterest, settlement, rate, par, frequency, basis)

  • Standard spreadsheet function

accrint(issue, firstInterest, settlement, rate, par, frequency, basis, calcMethod)

  • Standard spreadsheet function

accrintm(issue, settlement, rate, par, basis)

  • Standard spreadsheet function

amordegrc(cost, datePurchased, firstPeriod, salvage, period, rate, basis, excelCompliant)

  • Standard spreadsheet function

amorlinc(cost, datePurchased, firstPeriod, salvage, period, rate, basis)

  • Standard spreadsheet function

and(values...)

  • Boolean and
  • Example: and(A.FLAG, [B:3]=5)

array(values...)

  • Constructs a sequence from given values
  • Example: sum(array(1,2,3))

average(range)

  • Computes the average of cells in a range (sum()/count())
  • Example: average([A:(1,2,3)])

averageif(range, condition)

  • Conditionally computes the average of cells in a range (sumif()/countif())
  • Example: averageif([A:all()], $cell.A.TEXT="test")

betadist(x, alpha, beta)

  • Standard spreadsheet function

betainv(probability, alpha, beta)

  • Standard spreadsheet function

ceiling(number, significance)

  • Returns a rounded up number
  • Example: ceiling([A:4])

char(number)

  • Standard spreadsheet function

choose(idx, values...)

  • Standard spreadsheet function

code(text)

  • Standard spreadsheet function

concat_values(values...)

  • Combines multiple ranges of values into a single range
  • Example: concat_values("Comment is: ", A.COMMENT)

concatenate(strings...)

  • Combines several text strings into one string
  • Example: concatenate("Comment is: ", A.COMMENT)

contains(array, row)

  • Returns true if an array contains the row
  • Example: contains([A:(3,4,5), A])

cos(n)

  • Standard spreadsheet function

count(range)

  • Counts the number of cells in a range
  • Example: count([A.all()])

countif(range, condition)

  • Conditionally counts the number of cells in a range
  • Example: count([A.all()], $cell > 3)

coupdays(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

coupdaysbs(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

coupdaysnc(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

coupncd(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

coupnum(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

couppcd(settlement, maturity, frequency, basis)

  • Standard spreadsheet function

cumipmt(rate, nper, pv, startPeriod, endPeriod, typ)

  • Standard spreadsheet function

cumprinc(rate, nper, pv, startPeriod, endPeriod, typ)

  • Standard spreadsheet function

data_source_ado(driver_dll_path, connection_string, select)

  • Construct a data source from a generic database query
  • Example: data_source_ado("Npgsql.dll", [Measures:01], "select * from table1")

data_source_csv_file(path, delimiter)

  • Create a data source from a CSV file on a local disk
  • Example: data_source_csv_file("C:\import.csv", char(9))

data_source_csv_file(path, delimiter, encoding)

  • Create a data source from a CSV file on a local disk
  • Example: data_source_csv_file("C:\import.csv", char(9), "utf-8")

data_source_csv_file(path, delimiter, encoding, header, skip_lines)

  • Create a data source from a CSV file on a local disk
  • Example: data_source_csv_file("C:\import.csv", char(9), "utf-8", "LIST_A_ID", 1)

data_source_csv_upload(delimiter)

  • Create a data source from a CSV file uploaded by a user
  • Example: data_source_csv_upload(char(9))

data_source_csv_upload(delimiter, encoding)

  • Create a data source from a CSV file uploaded by a user
  • Example: data_source_csv_upload(char(9), "utf-8")

data_source_csv_upload(delimiter, encoding, header, skip_lines)

  • Create a data source from a CSV file uploaded by a user
  • Example: data_source_csv_upload(char(9), "utf-8", "LIST_A_ID,LIST_A_LABEL", 1)

data_source_json_http(url, http_header, base_path, columns...)

  • Create a data source from a json web service
  • Example: data_source_json_http("https://...", "", "$.records[*]", "product_id=$.fields.product_id[0]")

data_source_map(source, header, expressions...)

  • Transforms a data source
  • Example: data_source_map(..., "year,month", year(datevalue(field("value_03"))), ...)

data_source_mysql(connection_string, select)

  • Create a data source from a MySQL query
  • Example: data_source_mysql("server=localhost;user=root;password=test", "select 1 list_a_id from dual")

data_source_xlsx_file(path)

  • Create a data source from an Excel file on a local disk
  • Example: data_source_xlsx_file()

data_source_xlsx_file(path, sheet_name)

  • Create a data source from an Excel file on a local disk
  • Example: data_source_xlsx_file("C:\import.xlsx")

data_source_xlsx_file(path, sheet_name, header, skip_lines)

  • Create a data source from an Excel file on a local disk
  • Example: data_source_xlsx_file("C:\import.xlsx", "LIST_A_ID", 1)

data_source_xlsx_upload()

  • Create a data source from an Excel file uploaded by a user
  • Example: data_source_xlsx_upload()

data_source_xlsx_upload(sheet_name)

  • Create a data source from an Excel file uploaded by a user
  • Example: data_source_xlsx_upload("Sheet 1")

data_source_xlsx_upload(sheet_name, header, skip_lines)

  • Create a data source from an Excel file uploaded by a user
  • Example: data_source_xlsx_upload("Sheet 1", "LIST_A_ID,LIST_A_LABEL", 1)

date(y, m, d)

  • Returns the date, given the year, month and day of the month
  • Example: date(1990, 2, 20)

datevalue(date_text)

  • Standard spreadsheet function

datevalue(date_text, format)

  • Standard spreadsheet function

day(date)

  • Returns the day of the month for a date
  • Example: day([A:3])

db(cost, salvage, life, period)

  • Standard spreadsheet function

db(cost, salvage, life, period, month)

  • Standard spreadsheet function

ddb(cost, salvage, life, period)

  • Standard spreadsheet function

ddb(cost, salvage, life, period, factor)

  • Standard spreadsheet function

disc(settlement, maturity, pr, redemption, basis)

  • Standard spreadsheet function

dollarde(fractionalDollar, fraction)

  • Standard spreadsheet function

dollarfr(decimalDollar, fraction)

  • Standard spreadsheet function

duration(settlement, maturity, coupon, yld, frequency, basis)

  • Standard spreadsheet function

edate(date, months)

  • Adds a number of months to a date
  • Example: edate([A:3], 3)

effect(nominalRate, npery)

  • Standard spreadsheet function

element(d)

  • Converts a row reference stored in a cell to a row
  • Example: element([A:3]).COLUMN1

eomonth(date, months)

  • Standard spreadsheet function

exp(n)

  • Returns e raised to the power n
  • Example: exp([B:3])

export_items(list, file_path)

  • Export list items to a local file
  • Example: export_items('List B', "c:\temp\test.csv")

export_values(worksheet, file_path, single_value_column)

  • Export worksheet values to a local file
  • Example: export_values('Sheet 1', "c:\temp\test.csv", 0)

false()

  • Standard spreadsheet function

fdist(x, degreesFreedom1, degreesFreedom2)

  • Standard spreadsheet function

field(field_name)

  • Standard spreadsheet function

filter_by_range(range, condition_range)

  • Returns values from the first range if the corresponding value from the second range is nonzero.
  • Example: filter_by_range([A.all(), Measure:02], [A.all(), Measure:03])

filter_cells(range, condition)

  • Returns only those cells of a range which meet a condition
  • Example: filter_cells([A.all()], $cell.COLUMN>3)

filter_values(range, condition)

  • Returns only those values of a range which meet a condition
  • Example: filter_values([A.all()], $value>30)

find_item(list, filter)

  • Standard spreadsheet function

finv(probability, degreesFreedom1, degreesFreedom2)

  • Standard spreadsheet function

floor(number, significance)

  • Returns a rounded down number
  • Example: floor([A:4], 1)

forecast(forecastPoint, yvalues, xvalues)

  • Fits a straight line to data using linear regression and returns a point on that line
  • Example: forecast(3, H2.[B:4], H2.[B:5])

formatbool()

  • Renders a checkbox inside a cell
  • Example: formatbool()

formatbutton(label, updates...)

  • Renders a button inside a cell. Clicking the button will assign values to cells relative to the button location.
  • Example: formatbutton("Submit", update([ACC:19],1))

formatnum(format_string)

  • Formats a numeric cell according to a specified format string
  • Example: formatnum("0.00")

formatrange(min, max, step)

  • Renders a range slider inside a cell
  • Example: formatrange(0, 100, 5)

formattext()

  • Renders a textbox inside a cell
  • Example: formattext()

formattext(text)

  • Shows readonly text inside a cell
  • Example: formattext(A.COMMENT)

fv(rate, nper, pmt, pv, typ)

  • Standard spreadsheet function

fvschedule(principal, schedule)

  • Standard spreadsheet function

gammadist(x, alpha, beta, cumulative)

  • Standard spreadsheet function

gammainv(probability, alpha, beta)

  • Standard spreadsheet function

if(condition, val1, val2)

  • Returns one of two values, depending on a boolean condition
  • Example: if(A=A:3, [B:3], 4)

iferror(value, value_if_error)

  • Return a certain value if a formula evaluates to an error
  • Example: iferror([A:3]/[B:3], -1)

import_denorm(worksheet, data_source, single_value_column)

  • Import list items and worksheet values from a denormalized data source
  • Example: import_denorm('Sheet 1', data_source_csv_upload(char(9)), false())

import_items(list, data_source)

  • Import list items from a data source
  • Example: import_items('List B', data_source_mysql(...))

import_values(worksheet, data_source)

  • Import worksheet values from a data source
  • Example: import_values('Sheet 1', data_source_csv_upload(char(9)))

import_values(worksheet, data_source, single_value_column)

  • Import worksheet values from a data source
  • Example: import_values('Sheet 1', data_source_csv_upload(char(9)), true())

int(number)

  • Standard spreadsheet function

intrate(settlement, maturity, investment, redemption, basis)

  • Standard spreadsheet function

ipmt(rate, per, nper, pv, fv, typ)

  • Standard spreadsheet function

irr(values)

  • Standard spreadsheet function

irr(values, guess)

  • Standard spreadsheet function

isna(item)

  • Check if a list item has no value
  • Example: isna(Product.KIND)

ispmt(rate, per, nper, pv)

  • Standard spreadsheet function

join_item(item, column)

  • Returns a value of a reference column
  • Example: join_item(element([Measures:03]), PRODUCT_GROUP)

len(text)

  • Standard spreadsheet function

list_append(list, caption)

  • Appends a new list item to the end of a list
  • Example: list_append('List A', "New item: " & [Measure:02])

list_append(list, caption, id)

  • Appends a new list item to the end of a list
  • Example: list_append('List A', "New item: " & [Measure:02])

list_append(list, caption, id, parent)

  • Appends a new list item to the end of a list
  • Example: list_append('List A', "New item: " & [Measure:02])

list_append(list, caption, id, parent, linked_item)

  • Appends a new list item to the end of a list
  • Example: list_append('List A', "New item: " & [Measure:02])

list_build_relation(list, condition)

  • Standard spreadsheet function

list_delete(items)

  • Deletes list items
  • Example: list_delete('List A')

list_set_attr(items, attr, value)

  • Standard spreadsheet function

ln(n)

  • Returns the natural logarithm of n
  • Example: ln([B:3])

log(n)

  • Returns the logarithm of n to the base 10
  • Example: log([B:3])

log(n, b)

  • Returns the logarithm of n to the base b
  • Example: log([B:3], 3)

lower(s)

  • Converts a text string to lowercase
  • Example: lower(A.TEXT_COLUMN)

map_cells(range, expression)

  • Maps each cell in a range to a numerical value.
  • Example: map(H1.[A.join(B)], $cell.B.NUMBER_COLUMN)

map_values(range, expression)

  • Maps each value in a range to a new numerical value.
  • Example: map(H1.[Measure:01], if($value > 3, $value, 0))

match(searchitem, seq)

  • Returns the position of a search item in a sequence
  • Example: match(3, H2.[B:4])

match(searchitem, seq, matchtype)

  • Returns the position of a search item in a sequence
  • Example: match(3, H2.[B:4], -1)

max(range)

  • Returns the maximum of a cell range
  • Example: max([A.join(B)])

max(values...)

  • Standard spreadsheet function

mduration(settlement, maturity, coupon, yld, frequency, basis)

  • Standard spreadsheet function

median(values...)

  • Standard spreadsheet function

min(range)

  • Returns the minimum of a cell range
  • Example: min([A.join(B)])

min(values...)

  • Standard spreadsheet function

mirr(values, financeRate, reinvestRate)

  • Standard spreadsheet function

mod(number, divisor)

  • Standard spreadsheet function

month(date)

  • Returns the month for a date
  • Example: month([A:3])

nominal(effectRate, npery)

  • Standard spreadsheet function

normdist(x, mean, standardDev, cumulative)

  • Standard spreadsheet function

norminv(probability, mean, standardDev)

  • Standard spreadsheet function

normsdist(z)

  • Standard spreadsheet function

normsinv(probability)

  • Standard spreadsheet function

not(val)

  • Negates a boolean value
  • Example: not(A.FLAG)

now()

  • Returns the current time and date
  • Example: now()

nper(rate, pmt, pv, fv, typ)

  • Standard spreadsheet function

npv(rate, values)

  • Standard spreadsheet function

oddfprice(settlement, maturity, issue, firstCoupon, rate, yld, redemption, frequency, basis)

  • Standard spreadsheet function

oddfyield(settlement, maturity, issue, firstCoupon, rate, pr, redemption, frequency, basis)

  • Standard spreadsheet function

oddlprice(settlement, maturity, lastInterest, rate, yld, redemption, frequency, basis)

  • Standard spreadsheet function

oddlyield(settlement, maturity, lastInterest, rate, pr, redemption, frequency, basis)

  • Standard spreadsheet function

offset(seq, offset)

  • Skips the first 'offset' elements of a sequence. Result is a sequence.
  • Example: offset(H2.[B:4], 3)

offset(seq, offset, size)

  • Skips the first 'offset' elements of a sequence and takes 'size' elements which follow. Result is a sequence.
  • Example: offset(H2.[B:4], 3, 2)

or(values...)

  • Boolean or
  • Example: or(A.FLAG, [B:3]=5)

ordinal(d)

  • Returns the row's position (1,2,3...) in a table
  • Example: ordinal(A)

percentile(array, k)

  • Standard spreadsheet function

percentrank(array, x)

  • Standard spreadsheet function

pi()

  • Standard spreadsheet function

pmt(rate, nper, pv, fv, typ)

  • Standard spreadsheet function

power(number, power)

  • Returns a number raised to a power
  • Example: power([A:3], 2)

ppmt(rate, per, nper, pv, fv, typ)

  • Standard spreadsheet function

price(settlement, maturity, rate, yld, redemption, frequency, basis)

  • Standard spreadsheet function

pricedisc(settlement, maturity, discount, redemption, basis)

  • Standard spreadsheet function

pricemat(settlement, maturity, issue, rate, yld, basis)

  • Standard spreadsheet function

print(value)

  • Standard spreadsheet function

pv(rate, nper, pmt, fv, typ)

  • Standard spreadsheet function

quartile(array, quant)

  • Standard spreadsheet function

rand()

  • Returns a random number between 0 and 1
  • Example: rand() * 10

rate(nper, pmt, pv, fv, typ)

  • Standard spreadsheet function

rate(nper, pmt, pv, fv, typ, guess)

  • Standard spreadsheet function

received(settlement, maturity, investment, discount, basis)

  • Standard spreadsheet function

rept(text, number_times)

  • Standard spreadsheet function

round(number)

  • Returns a rounded number
  • Example: round([A:4])

round(number, places)

  • Returns a number rounded to a certain precision
  • Example: round([A:4], 2)

sin(n)

  • Standard spreadsheet function

sln(cost, salvage, life)

  • Standard spreadsheet function

sqrt(n)

  • Returns the square root of n
  • Example: sqrt([B:3])

style_background_color(color)

  • Standard spreadsheet function

style_background_color_readonly(color)

  • Standard spreadsheet function

style_border_bottom_color(color)

  • Standard spreadsheet function

style_border_left_color(color)

  • Standard spreadsheet function

style_border_right_color(color)

  • Standard spreadsheet function

style_border_top_color(color)

  • Standard spreadsheet function

style_color(color)

  • Standard spreadsheet function

style_font_family(fontFamily)

  • Standard spreadsheet function

style_font_size(fontSize)

  • Standard spreadsheet function

style_font_style(fontStyle)

  • Standard spreadsheet function

style_font_weight(fontWeight)

  • Standard spreadsheet function

style_padding_left(padding)

  • Standard spreadsheet function

style_padding_right(padding)

  • Standard spreadsheet function

style_text_align(align)

  • Standard spreadsheet function

style_width(width)

  • Standard spreadsheet function

substitute(text, old_text, new_text)

  • Standard spreadsheet function

sum(range)

  • Sums the contents of cells in a range
  • Example: sum([A:(1,2,3)])

sumif(range, condition)

  • Conditionally sums the contents of cells in a range
  • Example: sumif([A:(1,2,3)], $cell > 3)

sumproduct(range1, range2)

  • Returns the sum of the products of two cell ranges
  • Example: sumproduct([A.all(), B:3], [A.all(), B:4])

switch(values...)

  • Standard spreadsheet function

syd(cost, salvage, life, per)

  • Standard spreadsheet function

tan(n)

  • Standard spreadsheet function

tbilleq(settlement, maturity, discount)

  • Standard spreadsheet function

tbillprice(settlement, maturity, discount)

  • Standard spreadsheet function

tbillyield(settlement, maturity, pr)

  • Standard spreadsheet function

tdist(x, degreesFreedom, tails)

  • Standard spreadsheet function

text(m)

  • Returns a caption for a row
  • Example: text(A)

text(d)

  • Convert a number to text
  • Example: text(3)

text(m, mode)

  • Returns text for a row (use mode=1 for id and mode=2 for caption)
  • Example: text(A, 2)

tinv(probability, degreesFreedom)

  • Standard spreadsheet function

today()

  • Returns the current date
  • Example: today()

trim(text)

  • Standard spreadsheet function

true()

  • Standard spreadsheet function

trunc(number)

  • Standard spreadsheet function

trunc(number, num_digits)

  • Standard spreadsheet function

update(destination, source)

  • Only usable inside formatbutton(). Assigns values to a cell range (relatively to a button)
  • Example: update([A:3], [A:5] + 4)

upper(s)

  • Converts a text string to uppercase
  • Example: upper(A.TEXT_COLUMN)

validate(value, condition, message)

  • Marks a cell as invalid if a certain condition is false. Returns the first argument.
  • Example: validate([B:1]+3, $value <= 3, "result greater than 3")

value(d)

  • Converts a row's identifier to a numeric representation.
  • Example: value(A)

value(s)

  • Returns a number, given a text representation
  • Example: value(A.TEXT_COLUMN)

vdb(cost, salvage, life, startPeriod, endPeriod)

  • Standard spreadsheet function

vdb(cost, salvage, life, startPeriod, endPeriod, factor)

  • Standard spreadsheet function

vdb(cost, salvage, life, startPeriod, endPeriod, factor, noSwitch)

  • Standard spreadsheet function

weekday(date)

  • Returns the weekday for a date
  • Example: weekday([A:3])

weekday(date, type)

  • Returns the weekday for a date
  • Example: weekday([A:3], 2)

weeknum(date)

  • Returns the week number of a date
  • Example: weeknum([A:3])

weeknum(date, type)

  • Returns the week number of a date
  • Example: weeknum([A:3], 2)

with($variable=value1, value2)

  • Assigns value1 to a variable. Than computes value2 using the assigned variable.
  • Example: with($x=[B:1], $x*$x)

xirr(values, dates)

  • Standard spreadsheet function

xirr(values, dates, guess)

  • Standard spreadsheet function

xnpv(rate, values, dates)

  • Standard spreadsheet function

year(date)

  • Returns the year for a date
  • Example: year([A:3])

yearfrac(date, endDate)

  • Standard spreadsheet function

yearfrac(startDate, endDate, basis)

  • Standard spreadsheet function

yield(settlement, maturity, rate, pr, redemption, frequency, basis)

  • Standard spreadsheet function

yielddisc(settlement, maturity, pr, redemption, basis)

  • Standard spreadsheet function

yieldmat(settlement, maturity, issue, rate, pr, basis)

  • Standard spreadsheet function

zip_values(range1, range2, expression)

  • Zips two ranges of values into a single one. Both ranges must be of the same size.
  • Example: zip_values(W01.[Measure:01], W01.[Measure:02], if($value1>3, $value2, 0)

zip_values(range1, range2, range3, expression)

  • Zips three ranges of values into a single one. All ranges must be of the same size.
  • Example: zip_values(W01.[Measure:01], ..., if($value1>3, $value2, $value3)