Formulas are some of the most powerful tools in Streak, but they can also be very complicated. To make getting started a bit easier, we've assembled some of our most common formulas here.
Getting started
A formula column doesn't contain any information itself, so it's important to reference columns that have information in them. This will turn over your formula column into a perfect copy of whatever column in your pipeline you've referenced. You can do this like so:
= $'Column name'
You can change and mutate this reference by using mathematical operations. Here are some examples:
Find deal size by "Number of units" and "Price per unit" columns:
= $'Number of Units' * $'Price per unit'
Calculate the "Expected deal size" by the Probability of close and the Deal size:
= $'Probability of Close' * $'Deal Size'
Formula columns can also be powered by logic. You can use a simple if and else statement to evaluate a true or false result:
= if($'Number of Units' > 5)
{ return true; }
else
{ return false; }
You can combine multiple if statements by using else if and else, too. Combining them can lead to automatic calculation for something like the probability of a deal closing.
Return probability of deal close based on Stage
= if ($'Stage' === 'Lead')
{return 0.1;}
else if ($'Stage' === 'Pitched')
{return 0.3;}
else if ($'Stage' === 'Negotiating')
{return 0.7;}
else if ($'Stage' === 'Closed')
{return 1;}
else
{return "N/A";}
Pull specific parts of a name
If you have a full name in a column but need to mail merge on a first name, this formula will separate (splice) it into parts.
= $'[Column name]'.substring(0, $'Name'.indexOf(' '));
Only the last name
= $'[Column name]'.split(" ").slice(-1)
Count the number of objects associated with a box
We use this internally to keep track of how many reports relate to a single box (i.e. Feature Requests).
The prerequisite for this formula is a column that has objects in it (e.g. All Linked Boxes, Assignees, Contacts, or Tags). An example is:
= $'All Linked Boxes'.length
This formula can be used to count the number of objects in a column, too (the number of Contacts, for instance).
= $'Assigned To'.length
It can also be used to count the number of tags are being used in a column.
Data-correct inputs for common errors
Streak can only create summaries when data are all of the same type. If a Deal Size or Money related column has characters in it that are not numbers from 0-9 – e.g. commas or currency symbols – it will be unable to summarize the data for reporting purposes.
You can correct this for integers by using a formula column that guards this:
= var c = $'[Column]'||''
else {
return c.replace(/[^[0-9]]*/g, "")
}
Round numbers
= return Math.round($'[Column]');
Streak-specific functions
Working with dates in JavaScript is painful, so we've added a few convenience functions to simplify this.
Please note that when a date is specified in an example, Streak is expecting a JavaScript date and not a string like "December 31, 2018" to be written there.
Find the difference between two dates
Streak.secondDifference(date1, date2)
-- returns the number of seconds between two dates
Streak.minuteDifference(date1, date2)
-- returns the number of minutes between two dates
Streak.hourDifference(date1, date2)
-- returns the number of hours between two dates
Streak.dayDifference(date1, date2)
-- returns the number of days between two dates
Streak.weekDifference(date1, date2)
-- returns the number of weeks between two dates
Streak.monthDifference(date1, date2)
-- returns the number of months between two dates
Streak.yearDifference(date1, date2)
-- returns the number of years between two dates
Streak.workdayDifference(date1, date2)
-- returns the number of non-weekend days between two dates
Functions that add days to a date
Streak.addDays(date1, numDays)
-- this will add numDays to date1
Streak.addWorkdays(date1, numDays)
-- this will add numDays worth of workdays to date1
Format dates
Our formatting options can be expanded by using a formula to pull more information into the spreadsheet view.
Get the month from a date
= var date = new Date($'[Date column]');
return date.getMonth()+1;
Get the year from a date
= var date = new Date($'[Date column]');
return date.getFullYear();
Get the calendar week from a date
= var d = new Date($'Date of Last Stage Change');
function getWeek(d) {
var target = new Date(d.valueOf());
var dayNr = (d.getDay() + 6) % 7; target.setDate(target.getDate() - dayNr + 3);
var jan4 = new Date(target.getFullYear(), 0, 4);
var dayDiff = (target - jan4) / 86400000;
var weekNr = 1 + Math.ceil(dayDiff / 7);
return weekNr;
}
return getWeek(d);
Track days to close
By combining some of the functions and logic above, you can also create logic that allows you to track how long a deal took to close. This can be modified for use in other cases as well – e.g. tracking the number of days it took to get a contract signed, etc.
The only requirement is that you have at least one Date column that signals when the event occurred.
=
var open = $'Date Created' // this is a Magic Column
var close = $'Date Closed' // this is a Custom Column
if(!closed) { return open }
else { return Streak.dayDifference(open,close) }
For this formula column, set the formatting to # of Days Since and you're good to go – you have a column that will return either the number of days since open or the number of days between open and close .
Unsupported functions
Streak's formula functionality is built on Rhino, which is lightly limited in its functionality. You'll find that most common ES5 functions will work, but there is a limit there, too: we do not support XHR or any HTTP requests from formula.
Because of the way formulas are computed, we also advise against using a function like Date.now(), which will not update daily.