Using Formulas For Powerful Pipeline Calculations

Adding formulas to your Pipelines is a powerful new way to gain even more insight into your processes. There are an unlimited number of uses for formulas- customize Streak to your specific use case. For example:

  • For Sales Pipelines, formulas can be created for lead scoring
  • For Dealflow Pipelines, formulas can be created for % ownership of an investment based on the total capital raised and the investment amount
  • For Bug Tracking Pipelines, formulas can be made for the time it takes a bug to go from reported to closed

Getting Started

Formula Columns are special columns can be added from the same menu as a Custom Columns. Get started by clicking on a column header and inserting a new column of type "Formula". You're now ready to enter a formula. Streak formulas use javascript with return statements for calculations. You can get started right now, for free, with up to 2 formulas. Users of our paid Professional plan enjoy an unlimited number of formulas.

Ways To Use Formula Columns

The formula below simply adds 3 days to dates in a given column:

= if($'Trial Start Date'=='' || $'Trial Start Date' == null)
    {return null}
else
    {Streak.addDays($'Trial Start Date', 3)}

The formula to calculate a 3-day follow up, while accounting for weekends, is:

= if($'Trial Start Date'=='' || $'Trial Start Date' == null)
    {return null}
else if($'Trial Start Date'.getDay() >= 3 && $'Trial Start Date'.getDay() <= 5)
    {Streak.addDays($'Trial Start Date', 5)}
else if($'Trial Start Date'.getDay() == 0 || $'Trial Start Date'.getDay() == 6)
    {Streak.addDays($'Trial Start Date', 4)}
else
    {Streak.addDays($'Trial Start Date', 3)}

In the formula column, enter the following formula to calculate the hour difference between both dates 

= Streak.hourDifference($'Trial Start Date', $'Follow Up')

If you want to calculate the difference in days instead, try this:

= Streak.dayDifference($'Trial Start Date', $'Follow Up')

Verify 'Deal Size' field is greater than 0:

= if ($'Deal Size') { 
    if($'Deal Size' < 0) {
        return false;
    } 
    else {
        return true;
    } 
} 
else { 
    return true;
}

Making The Most Of Formula Columns

Use 2 equal signs in IF statements for equals: 

if($'Number of Units' == 5)
= if($'Status' == 'Paid'){
    return 0;
}
else{
    return 1;
}

Use AND/OR in IF statements:

if($'Number of Units' < 5 || $'Number of Units' > 10))
if($'Number of Units' > 5 && $'Lead Source' == 'Web'))

Use ELSE IF statements:

if($'Number of Units' < 5){
    return 'small';
}
else if ($'Number of Units' < 10){
    return 'medium';
}

else {
    return 'large';
}

Use the "parseFloat" function to add two Free Form Column numbers together:

= parseFloat($'freeform column1') + parseFloat($'freeform column2') 

Represent both null and blank options when working with Free Form Columns:

= if ($’Freeform Column 1' != null && $'Freeform Column 1' != '’){
    return "valid";
}
else {
    return "invalid";
}

UP NEXT: Using Saved Views For Pipeline Organization

Contact Us