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:
- In Insight, click New > Analysis and select a subject area.
- In the Subject Areas panel, select the columns you want to include in your formula.
- Click the Results tab.
- Click New Calculated Measure . The New Calculated Measure dialog opens.
- 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.
- 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:
- 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.
- Click OK.
Your formula is displayed in the column's heading.
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.
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.
To convert a column's data type:
- Click on the Results tab.
- Click on .
The New Calculated Measure dialog opens.
- Click on f(...).
The Insert Function dialog opens.
- Under Conversion, double-click Cast.
CAST(expr AS type) displays in the Column Formula section.
- 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:
- Click OK.
The data you converted is now rounded to two decimal places.