Overview

top

The formula builder assists you to write valid formulas for your view. Calculated fields written in this way can be used as normal fields within a report – i.e. all functions can be applied to them such as aggregations etc. This is not possible with SQL calculated fields. To begin you will need to work out what type of field your resulting value will require and select either Metric or Dimension.

Metric

This calculation returns numeric values and allows for standard Metric formatting options such as decimal places, prefix, suffix, and default aggregation.

Dimension

This calculation allows for all the standard Dimension formatting and functionality such as inclusion in Drill Down Hierarchies.

Creating a Calculation

  1. Drag in the appropriate Formula Builder field type (Metric or Dimension), assigning it to a Field Category.
  2. Navigate to the Formula tab
  3. The formula builder will allow you to generate a valid formula that will be returned by this column. Highlighted buttons will help guide you to create a valid formula.
  4. Test your formula by clicking the test formula link.
  5. Save & Activate

Case Statements

top

More complex calculations can be created using the case statement. The case statement allows you to create new values in columns based on business logic. For example IF age is less than 20 then print “Young”.

Case Statement Structure

If you are not familiar with Case Statements, the basic structure is as follows:

CASE 
	WHEN this is true THEN return this
	WHEN this is true THEN return this
	WHEN this is true THEN return this
	...
	ELSE return this
END

Each of the items in CAPS are a component in the case statement:

Component

Description

CASE

The calculation will always begin with the CASE component. Make sure you have added this before trying to build the formula, other components will not be available until you do.

WHEN

You can have as many WHEN components as you like in a Case statement, but they will always need to be before the ELSE component. WHEN is used to define a condition, for example:
WHEN AthleteAge < 21
This identifies any rows where the AthleteAge value is less than 21. WHEN components always have a THEN so that Yellowfin knows what to return when the condition is met.

THEN

This is linked to the WHEN component and instructs Yellowfin on what to return when the condition is met. For example:
WHEN AthelteAge < 21 THEN 'Young'
This will mean that if the AthleteAge field is less than 21 the calculation will display the text 'Youth'.

ELSE

This is an optional component that is used to tell Yellowfin what to do when none of the WHEN conditions are met. It basically works like an extra THEN component.

END

The calculation will always end with an END component in order to tell Yellowfin that it's complete. Your calculation will not be valid if you don't have an END so be sure to add it.

Creating a Case Statement

  1. To insert a Case statement, click the CASE button in the Formula Builder.
  2. Click on the WHEN button will open a popup and create your first WHEN condition and THEN instruction.
    Note: it operates similar to the formula builder, only allowing formula objects to be inserted where they are valid.
  3. Click OK to complete your condition/instruction
  4. Continue adding WHEN conditions until you have covered all the possibilities required
  5. Add an ELSE condition if needed, this is optional, but recommended.
  6. Add your END component
  7. Save & Activate your calculation

Grouped Values

top

This allows you to group the values of a field. For example:

Demographic

Rating

Sport

2.5

Culture

5

Family

6.5

Group Rating values under new labels.

Demographic

Rating

Sport

Poor

Culture

Standard

Family

Standard

 

Creating a Grouped Values Field

In order to set up a Grouped Field complete the following (this example will create three groupings with text labels):

  1. Drag in Grouped Values field into a Category in the Available Fields panel
  2. Select a Field to base the grouping on
  3. Supply a Business Name
  4. Navigate to the Values tab
  5. Click on Add Group
    1. Poor Between 0 AND 3
    2. Click Save
  6. Click on Add Group
    1. Standard Between 4 AND 7
    2. Click Save
  7. Click on Add Group
  8. Save & Activate your field

 

top