Creating columns with custom formulas

To help you make data in an analysis more meaningful, Oracle Eloqua Insight allows you to create columns using custom formulas.

Important: An Insight Analyzer license is required to create columns with custom formulas. Learn more about Insight users and permissions.

Tip: If a custom formula doesn't calculate the way you expect, you might need to convert your columns' data type.

To create a column with a custom formula:

  1. In Insight, click New > An image of the Analysis icon Analysis and select a subject area.
  2. In the Subject Areas panel, select the columns you want to include in your formula.

    An image of the Subject Areas panel

  3. Click the Results tab.
  4. Click New Calculated Measure An image of the New Calculated Measure icon. The New Calculated Measure dialog opens.
  5. In the Column Formula section, enter your custom formula. You can use the components at the bottom of the Column Formula section to help you build the formula, or type it in yourself. A subject area is the table name and the attributes and metrics are columns.

    An animation showing an example of a custom formula

    • f(. . .): Use this button to select a function from a tree of function groups. For example, you can build a formula based on a SQL function, such as RANK("Sales Measures"."Dollars"). After clicking this button, choose any function in the Insert Function dialog to see its syntax, where you can use it, a description, and an example:

      An image of the Insert Function dialog

    • Filter. . .: Use this button to add a SQL filter. For example, you can build a formula that uses the SQL FILTER function to filter the data, such as FILTER("Sales Measures"."Dollars" USING ("Markets"."Region" = 'EASTERN REGION').
    • Column: Use this button to select a column to use in the formula from a list of the available attribute and measure columns that are included in the analysis criteria .
    • Variable: Use this button to select the type of variable (Session, Repository, Presentation, or Global) to add. For example, you can build a formula using a presentation variable, such as "Sales Measures".Dollars + @{PercentIncrease}{1.50}, where PercentIncrease is the name of the presentation variable and 1.50 is its default value.
    • +: Use this button to insert the addition operator to add values.
    • -: Use this button to insert the subtraction operator to subtract values.
    • x: Use this button to insert the multiplication operator to multiply values.
    • /: Use this button to insert the division operator to divide values.
    • %: Use this button to insert the percent sign to divide values by 100.
    • (: Use this button to insert the left parenthesis to enclose data in brackets.
    • ): Use this button to insert the right parenthesis to enclose data in brackets.
    • ||: Use this button to insert the concatenation operator.
  6. Click OK.

    Your formula is displayed in the column's heading.

    An image of a custom formula column

Converting a column's data type

Certain formulas require that you convert a column's data type. For example, your columns might contain integers while your formula needs decimal places to calculate the way you expect. If we calculate a percentage with the following formula, Total Soft Bouncebacks / Total Bouncebacks * 100, Insight gives us a sum of zero because the data in our columns are integers.

An image that demonstrates why using integers to calculate percentages won't work

The solution is to convert the integers to floats. In Oracle Eloqua Insight, you can use the SQL CAST function to convert data types. In this topic, we only use floats and integers. To learn more about the CAST and other conversion functions, see Logical SQL Reference.

An image of columns with data converted from integer to float

To convert a column's data type:

  1. Click on the Results tab.
  2. Click on An image of the New Calculated Measure icon.

    The New Calculated Measure dialog opens.

  3. Click on f(...).

    The Insert Function dialog opens.

  4. Under Conversion, double-click Cast.

    CAST(expr AS type) displays in the Column Formula section.

    An image of the Cast function

  5. Replace expr with the column you want to convert, and replace type with the data type the column should be converted to.

    Here is the completed formula:

    An image of a custom formula with data type conversion

  6. Click OK.

    The data you converted is now rounded to two decimal places.

    7. An image of Total Opens converted to float

Learn more

Insight for analyzers

Building your first analysis

Adding formatting to your analysis