Formulae 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 formula here.

Getting started

A formula column doesn't contain any information itself, so it's important to reference columns that do (or might) have information in them. You can do this like so:

= $'[Column name]'

This will turn your formula column into a perfect copy of whatever column in your pipeline you've referenced.

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 column 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 is data-correct:

= if $'[Column]' % 1 === 0) { 
    return $'[Column]'
}

else {
    return $'[Column]'.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);

Did this answer your question?