Custom variable, created variable, UCA, User created axis
Harmoni allows you to create new items or customized constructions to suit your analysis and reporting requirements. New variables can be created using items that already exist in the project, or with user-entered values.
You can create a new item by first selecting the desired position in the project tree and then choosing one of the options under construct in the design menu. Learn more about the design menu.
Constructions can use items in one or many sources. Constructions automatically update when new data sources are added.
In this article
- Expression Editor
- Expression Editor Operators
- Expression Editor Conditions and Functions
- Save and Apply Expression
- View the Definition
- Aggregation Type (Multi-Level only)
1. Expression Editor
The expression editor is the interface by which the new item definition is entered. The expression editor supports typing and drag and drop to build the definitions.
Follow these steps to open the expression editor:
- Select a position in the project tree where the new item is required. Remember that, if needed, the new item can be moved after creating it.
- Select the ‘new item’ option under construct in the design menu, e.g., new axis.
- A new, unlabeled axis appears in the tree. Type the new label name and press Enter.
- After pressing Enter, a new unlabeled element appears. Type the new element label and press Enter. Note that it is important that you do not click anywhere before entering the element label, or the new axis will be removed.
Once the new item is labeled, the Expression Editor will open, ready for the new definition.
After you begin typing in the expression editor text box, a drop-down list displays the available matching axes within your project tree.
The possible matches are displayed when they are similar.
If you enter the exact axis match, the expression editor will automatically display a "." for you to add the corresponding item into the definition.
- The possible options are displayed for selection.
- Use the arrow down key to highlight the option choice, and Enter to select it.
- Existing elements in the dictionary are used in the definition.
- The most reliable way of including an item in a complex definition is by typing to display the list of matching labels and then selecting them from the displayed list.
Drag and Drop
- An element can be defined using a single Axis.Element condition by dragging a single element into the expression editor.
- Dragging in more than one element from the same axis into the definition assumes an OR relationship across elements.
- Dragging in elements from different axes into the definition assumes an AND relationship.
When an expression is saved, the quotes may be stripped (they’re only required when the label contains special characters such as commas, brackets. etc.)
Please keep in mind that when elements contain characters which are used by Harmoni when creating definitions, for example, and/or, brackets ( ), or full stops ., it can be necessary to use drag and drop rather than typing when creating your construction. This will avoid any confusion in the definition and allow you to Save or Apply the expression.
2. Expression Editor Operators
a) Boolean logic operators
- AND decreases the number of data records included.
- Both conditions in an AND relationship must exist in at least one source, or no records will be included in the construction.
- OR increases the number of data records included.
- Not all conditions in an OR relationship need to exist in any source for records to be included in the construction.
- NOT is to be used with either AND or an OR operator.
- The definition after NOT must include the axis name before the element name.
AxisName.("(ElementName1)" or "(ElementName2)") and not Axis_Name."(ElementName3)"
- Brackets are used to determine the order of calculation.
Let's assume you want to create a new "Target Segments" axis with the following element:
- Coast Focused Females = Females who visited a beach and a marine park/reserve but didn't visit a geothermal park, glacier, national park, or hot pools.
You have a combination of standard axes and elements with a series of boolean operators in this case.
AxisName1.ElementName1 or AxisName2.(ElementName1 and ElementName2 or ElementName3) and not AxisName2.(ElementName4 or... etc)
b) Comparative operators
Comparative operators need to be associated with a numerical value. This value can come from a measure or be defined using the count function.
Comparative operators include:
- Greater than >
- Less than <
- Less or equal <=
- Greater or equal >=
- Not equal <>
- Equal =
Let's assume you want to create a new "Target Segments" axis with the following element:
- Frequent Flyer Retirees = 65+ years who visited the moon more than twice.
Here you have a combination of a standard axis and a measure. Given that you have a measure, you need to use a comparative operator to be associated with a numerical value, in this case, more than two visits.
AxisName1.(ElementName1 or ElementName2 or ElementName3) and MeasureName1 > value
3. Conditions and Functions
a) If statements
If statements are typically used to populate a new measure or weight with a different value depending on specific conditions.
If statement syntax
|Syntax for if statement
if (Logic_Test, Value_if_True, Value_if_False)
|Syntax for nested if statement
|if (Logic_Test, Value_if_True, if (Logic_Test, Value_if_True, Value_if_False))
Let's assume you want to weight Males and Females to a 50/50 distribution. Across all sources, the Male/Female distribution is 45/55. To change this to 50/50, we need to assign all males a weight of 1.111 (50/45), and all females need a weight of 0.909 (50/55). For any missing cases (i.e., No answer for Male or Female/ Value if False), we are assigning a NULL.
b) Count responses
The count function defines a new element or a new measure by counting the number of responses given to the items listed in a comma-separated list. The count function creates a numeric value from an existing axis, element, or measure.
The numeric value generated from a count function can either be used in a new measure or be compared to another value to create a True/False result for use in a new element.
- Axis item(s) in the Count function count the number of elements in those axes that each respondent has a response in.
- Individual Axis.Element item(s) in the Count function will only ever generate a value of 0 or 1, as each respondent can only have a response to a specific element.
- Each Measure item in the count function will only generate a value of 0 or 1, as each respondent can only have one numerical response to a measure.
- To sum multiple items together, list them within the Count function using a comma separator.
- Boolean operators can specify relationships that must exist for a count to be included.
Count function syntax
a) Counting all responses in a standard axis.
With this syntax, the values count returns are the number of elements each respondent responded to. Note, that only multiple-response variables will ever give a count greater than 1.
- Element: count (AxisName1) >= 1
- Measure: count (AxisName1)
b) Count function with Boolean logic can be used to count whether a respondent mentioned at least one response to a specific set of items.
Using count ( _ or _ or _ )
With this syntax, the values count returns are 0 and 1. The maximum count the expression returns is 1.
- Element: count (AxisName1.(ElementName1 or ElementName2 or Element 3)) = 1
- Measure: count (AxisName1.(ElementName1 or ElementName2 or Element 3))
Using count ( _ , _ , _ )
With this syntax, you can sum together counts from a specific set of items. It evaluates the number of items a respondent has answered and adds the count for each. The maximum count the expression returns is the max number of possible responses.
- Element: count (AxisName1.ElementName1 , AxisName1.ElementName2 , AxisName1.ElementName3)) = 3
- Measure: count (AxisName1.ElementName1 , AxisName1.ElementName2 , AxisName1.ElementName3)
Let's look at some examples. Let's assume you want to find out the number of types of technology used during a trip.
In this case, you want to know how many respondents used one type, how many used two types, etc.
To avoid including None of these element in the count, the best syntax for this scenario is:
count ( _ , _ , _ ).
To exclude the responses for the element "None of these" the best syntax to use is count ( _ , _ , _ ). This will just consider the elements you want to include in the calculation.
Using the syntax count (AxisName1) would consider all elements.
c) Arithmetic calculations
Arithmetic calculations need to be associated with a numerical value. This value can come from a measure or can be defined using the count function.
Calculations in Harmoni include:
- Addition using +
- Subtraction using -
- Multiplication using *
- Division using /
Let's assume you want to create an "Other Costs" measure containing the amount spent that wasn’t on airfares or packages, i.e., the amount left after subtracting airfares and packages from total spend.
4. Save and Apply Expression
Once you have defined your new items in the expression editor Save Expression and Apply Expression will become active. This will only happen when Harmoni validates the conditions you have entered.
Save Expression will save your definitions but won't update data. To improve the workflow and efficiency when creating your custom variables, you can save your definitions as you go but only apply them when you are ready.
When an expression is saved, the quotes may be stripped (they’re only really required when the label contains special characters such as brackets).
Analysis data is only updated in new items when Apply Expression is applied to the axis.
Save Expression allows you to modify multiple expressions by delaying the data parse until you choose to apply. This gives the ability to control the actual moment that it gets applied.
If you are the Project Owner and have auto sync on, only Apply Expression is available as the expression is always applied to the data when sync is on.
5. View the Definition
To view the definition of a constructed item, select the item and click library/editor in the action pane at the bottom left of the Harmoni screen. The editor will open and you can view the full definition.
If you are the owner of the construction, hover over the definition to reveal the EDIT button. Click EDIT to make changes to your constructed item. Note that if you are the Project Owner, you need to click the edit pen prior to editing the project.
Why can't I see the definition?
If the Project Owner hides items in a project tree and these items are used in a construction, a Creator (non-Owner) or Explorer with General Access can not see the definition in the editor.
If the Creator or Explorer has Preview Access, clicking the preview icon to view the unpublished view of the project allows the user to see the hidden items and all construction definitions.
6. Aggregation Type (Multi-level only)
When creating a measure in a multi-level project, you can select the aggregation type. This changes the way the data are aggregated together.
The default option is Sum, and when you create the new measure in the expression editor, you can change the aggregation type to Avg, Min, or Max.
Select one of the aggregation types available in the expression editor when defining your new measure. Learn more.
Where to from here?
Learn more about different construction types.