Functions
A complete overview of all supported functions, including example use cases.
Your number format in Francis determines whether you’ll use commas (US) or semicolons (EU) to separate function parameters. All examples below use the US format with commas.
avg("row_name"[range_start:range_end])
avg("row_name"[range_start:range_end])
The avg() function calculates the average of the values in the specified time series from range_start to range_end.
avg_ytd("row_name")
avg_ytd("row_name")
The avg_ytd() function returns the average of the values in a time series from the beginning of the fiscal year until and including the current month.
avg_last_year("row_name")
avg_last_year("row_name")
The avg_last_year() function returns the average of the values in the previous fiscal year. This function is influenced by your fiscal year settings.
avg_last("row_name",num_months)
avg_last("row_name",num_months)
The avg_last() function returns the average of the values in the time series going back n months, as defined by the num_months parameter.
median("row_name"[range_start:range_end])
median("row_name"[range_start:range_end])
The median() function returns the median of the values in the specified time series from range_start to range_end
median_ytd("row_name")
median_ytd("row_name")
The median_ytd() function returns the median of the values in a time series from the beginning of the fiscal year until and including the current month.
median_last_year("row_name")
median_last_year("row_name")
The median_last_year() function returns the median of the values in the previous fiscal year. This function is influenced by your fiscal year settings.
median_last("row_name", num_months)
median_last("row_name", num_months)
The median_last() function returns the median of the values in the time series going back X months, as defined by the num_months parameter.
sum("row_name"[range_start:range_end])
sum("row_name"[range_start:range_end])
The sum() function calculates the total of the values in the specified time series from range_start to range_end.
sum_ytd("row_name")
sum_ytd("row_name")
The sum_ytd() function returns the sum of the values in a time series from the beginning of the fiscal year until and including the current month.
sum_last_year("row_name")
sum_last_year("row_name")
The sum_last_year() function returns the sum of the values in the previous fiscal year. This function is influenced by your fiscal year settings.
sum_last("row_name", num_month)
sum_last("row_name", num_month)
The sum_last() function returns the sum of the values in the time series going back X months, as defined by the num_months parameter.
receivables("row_name", payment_days)
receivables("row_name", payment_days)
The receivables() function accumulates revenue amounts from previous months based on an assumption of payment days. Based on the number of payment days, the function loops back through previous months to collect revenue as part of receivables.
The function assumes 30 days per month. The payment days input must be hardcoded and cannot reference a cell.
ignore_div_zero(a/b)
ignore_div_zero(a/b)
Theignore_div_zero() returns a zero if the function’s output is a #DIV!/0 error. The function is used for margin and percentage calculations, where the denominator is occasionally zero.
power(num, exponent)
power(num, exponent)
The power() function is a mathematical function that returns the value of the parameter num to the power of the exponent parameter.
min(a,b)
min(a,b)
The min() function returns the smallest numeric value of the input values.
max(a,b)
max(a,b)
The max() function returns the largest numeric value of the input values.
abs(num)
abs(num)
The abs() function returns the absolute value of a number.
round(num, decimals)
round(num, decimals)
The round() function rounds the number value up or down to the nearest integer. Optionally, provide desired number of decimals.
round_down(num)
round_down(num)
The round_down() function rounds numbers down to the nearest integer.
round_up(num)
round_up(num)
The round_up() function rounds numbers up to the nearest integer.
if(condition, value_if_true, value_if_false)
if(condition, value_if_true, value_if_false)
The if() function allows you to create a conditional calculation, similar to what is available in Excel or Google Sheets.
if_month(a,b)
if_month(a,b)
The if_month function returns 1 if the time period is equal to the month number input in the formula, else 0.
if_month(1)returns1if the month is January, else0if_month(1, 6)returns1if the month is January or June, else0if_month(1,6 , 12)returns1if the month is January, June, or December, else0
if_quarter_month(num)
if_quarter_month(num)
The if_quarter_month function returns 1 if the time period is equal to the month number inside a given quarter. E.g., you can input [1;3] into the formula.
if_quarter_month(1)returns1if the month is January, April, July, or Octoberif_quarter_month(2)returns1if the month is February, May, August, or Novemberif_quarter_month(3)returns1if the month is March, June, September, or December
weekdays()
weekdays()
The weekdays() functions returns the numbers of weekdays (Monday through Friday) in a given month.