All Collections
Pipelines
Formula columns
Frequently used JavaScript formulas
Frequently used JavaScript formulas

Kickstart your pipeline with our most common JavaScript formulas

Caitlyn Grundy avatar
Written by Caitlyn Grundy
Updated over a week ago

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.

Did this answer your question?