Formula Basics

Owned by Jennifer Koerber (Unlicensed)
Last updated: Jun 03, 2019

When building and editing analyses, it's easiest to work in the Criteria tab. When you're ready to finalize the view, switch to the Results (Compound Layout ) tab.

 

Formulas Overview

A formula can be defined on any type of column. You must create a column to put the formula into; it doesn't really matter what the column starts as, because its contents will be replaced by the results of the formula, but it can save you some editing time if you choose the same type of attribute or measurement as one of the columns you want the formula to act on.

If you have used formulas in Microsoft Excel or Cognos, working with formulas in Analytics will be familiar.


Add or Edit Formulas

To add or edit a formula:

  1. In the Criteria tab, choose an attribute or measurement to add and place it where you want it in the table
  2. Click on the column menu, then choose Edit formula
  3. Click on the f(...) button at the bottom of the Column Formula work area to choose an option from the Insert Function wizard. The wizard menu displays lists of all available formula functions, including the syntax, explanation, and a brief description for each.


  4. Choose a function for your formula and click OK
  5. Edit the formula in the work area:
    1. Manually, if you know exactly what you want
    2. Using the button options at the bottom of the work area to select columns and variables, add filters, and insert basic mathematical functions
  6. To customize the column heading, click on the Custom Headings box and modify the Column Heading field
  7. When finished, click OK
  8. Click again on the column menu to:
    1. Sort the table by the formula results
    2. Change other Column Properties, especially the Data Format (e.g., to treat a column's numbers as a percentage)
    3. To add Filters to that column
  9. When finished, click on the Results tab to see the new formula column's results

 

Practice Example

New Analysis Example (continued)

Using the analysis you created from scratch:

  1. Add another measurement column at the far right: Physical Item Details >> Num of Loans (In-house +Not In House)
  2. Click on the column menu, select Edit formula
  3. For this example, we'll enter the formula manually in the Column Formula work area. Copy and paste this formula into the box, replacing whatever is there:
    "Physical Item Details"."Num of Loans"/"Physical Item Details"."Num of Items"*100
  4. Click OK
  5. On the column menu, select Column Properties, then Data Format
  6. Check Override Default Data Format
  7. Select Percentage from the Treat Number As drop-down list, then click OK
  8. On the column menu, select Sort, then Descending
  9. Click on the Results tab to see the library with highest percentage circulation per item, with a percentage symbol applied on the calculated values

 

Â