Table of Contents

• How do I create a basic calculation?
• What are functions?
• How can I add custom fields to my data through calculations?
• How do I create conditional calculations?

How do I create a basic calculation?

A basic calculation might be "Deal Value * Deal Probability." To create this sort of calculated field, you'll want to first click on the "New Calculation" button under the Data tab of the report editor.

In the calculation window itself, the key area is the "Calculation" text area. For our particular calculation, we'll want to do the following:

[Deal Value] * [Deal Probability]

The bracket notation of [Field Name] is used to identify fields in the calculation, since a particular field name might include special characters itself. You can use any of the basic mathematical operators (+, -, *, and /), as well as using parantheses to further build out more complex order of operations.

What are functions?

Functions provide additional calculation capabilities beyond the basic math operators. Easy Insight has a wide range of functions including additional math functions, text functions, and conditional functions.

How can I add custom fields to my data through calculations?

Let's walk through the process of defining three custom fields on top of Highrise data, a fairly common use case: Probability (a measure), Sales Channel (a grouping), and Expected Close Date (a date).

First, we'll want to tweak some of our sample data to include these custom fields. For example, in the Deal Description field of Highrise, you could add the following:

            [Probability:50] [Channel:DirectMarketing] [ExpectedClose:2011-03-31]
        

With those values defined, we can step through building each of the necessary calculations. First, define a new calculation for the Probability field. Label it as Probability and use a calculation of:

            namedbracketvalue(Deal Description, "Probability")
        

This expression will use the namedbracketvalue function in Easy Insight to find the value for the custom field called Probability which is surrounded by brackets and labeled with a colon, as shown in the earlier example. If you're operating in a project management scenario instead of a CRM scenario, you could define a custom field called Expected Time by doing:

            namedbracketvalue(Todo Item Name, "Expected Hours")
        

Creating the derived grouping for Sales Channel will work almost identically--just make sure you start with New Derived Grouping instead of New Calculation.

            namedbracketvalue(Deal Description, "Channel")
        

Finally, we can create a derived date--this'll add one wrinkle in that we need to specify how the date itself will be parsed. We'll use date formatting to specify that format. For example, if we pass in 2011-03-31 as the date in the custom field, we'll specify a format of yyyy-MM-dd for Year, Month, and Day of Month. You can see how that works in the following example:

            namedbracketdate(Deal Description, "ExpectedClose", "yyyy-MM-dd")
        

How do I create conditional calculations?

There are currently three functions for doing conditional calculations--greaterthan, equalto, and notnull. Let's walk through each.

Here's an example of using the greaterthan function:

greaterthan([Deal Value], 5000, "Big Deal", "Small Deal")

The function compares the first and second arguments (Deal Value and 5000). If the value for the row is greater than 5000, the function will return a value of Big Deal. If the value for the row is less than or equal to 5000, the function will return a value of Small Deal.

Here's an example of using the equalto function:

equalto([Status], "Pending", "The deal is in stage 1", "The deal is in stage 2")

It follows the same pattern as the greaterthan function--compares the first and second arguments, if that evaluation is true, show the 3rd value, if that evaluation is false, show the 4th value.

Finally, the notnull function looks for the presence of any value in a field. For example:

notnull([Company Name], "Company " + [Company Name], [Contact Name], "Contact " + [Contact Name])

If the first value passed into notnull has a value, it returns the next value, so if the row has a value for Company Name, it returns Company (the name of the company). If no value is found, it proceeds to the next pair of arguments, so if Contact Name has a value, it displays Contact (the name of the contact).