Creating Calculated Measures and Members
This page describes how to create and use calculated measures and members in Business Intelligence.
Your cubes might contain additional calculated members that you can use in all queries; see Defining Models for InterSystems Business Intelligence.
Also see Accessing the BI Samples.
Overview of Calculated Measures and Members
In MDX, you can create a calculated member, which is a member based on other members. You can define two kinds of calculated members: ones that are measures and ones that are not. (Remember that a measure is considered to be a member of the MEASURES dimension.)
-
A calculated measure is based on other measures. For example, one measure might be defined as a second measure divided by a third measure.
The phrase calculated measure is not a standard MDX phrase. This documentation uses the phrase for brevity.
-
A non-measure calculated member typically aggregates together other non-measure members. Like other non-measure members, this calculated member is a group of records in the fact table.
For example, suppose member A refers to 150 records in the fact table, and member B refers to 300 records in the fact table. Suppose that you create a member C that aggregates A and B together. Then member C refers to the relevant 450 records in the fact table.
Creating a Calculated Member
To create one or more calculated members within a query, use syntax as follows:
WITH with_clause1 with_clause2 ... SELECT query_details
Notice that you do not include commas between the clauses.
Where:
-
Each expression with_clause1, with_clause2, and so on has the following syntax:
MEMBER MEASURES.[new_measure_name] AS 'value_expression'
Later sections of this page discuss value_expression.
-
query_details is your MDX query.
Then your query can refer the calculated member by name in all the places where you can use other members.
For example:
WITH MEMBER MEASURES.avgage AS 'MEASURES.[age]/MEASURES.[%COUNT]'
SELECT MEASURES.avgage ON 0, diagd.diagnoses.members ON 1 FROM demomdx
avgage
1 None 33.24
2 asthma 34.79
3 CHD 67.49
4 diabetes 57.24
5 osteoporosis 79.46
This calculated member is a query-scoped calculated member; its scope is the query. For information on session-scoped calculated members, see CREATE MEMBER Statement, in the InterSystems MDX Reference.
MDX Recipes for Calculated Measures
This section describes how to create MDX expressions for some commonly needed calculated measures:
Combinations of Other Measures
For a calculated measure, the value expression often has the form of a mathematical formula that combines measure expressions. For example:
(MEASURES.[measure A] + MEASURES.[measure B]) * 100
Or:
(MEASURES.[measure A] + MEASURES.[measure B])/MEASURES.[measure C]
More formally, in this expression, you can use the following elements:
-
References to measures.
-
Numeric literals. For example: 37
-
Percentage literals. For example: 10%
There must be no space between the number and the percent sign.
-
Mathematical operators. InterSystems IRIS Business Intelligence supports the standard mathematical operators: + (addition), - (subtraction), / (division), and * (multiplication). It also supports the standard unary operators: + (positive) and - (negative).
You can also use parentheses to control precedence.
For example: MEASURES.[%COUNT] / 100
-
MDX functions that return numeric values, such as AVG, MAX, COUNT, and others.
In addition to the functions already discussed, Business Intelligence supports several scalar functions: SQRT, LOG, and POWER.
The MDX function IIF is often useful in such expressions. It evaluates a condition and returns one of two values, depending on the condition. You can use this to avoid dividing by zero, for example.
Percentages of Aggregate Values
It is often necessary to calculate percentages of the total record count or percentages of other aggregate values. In such cases, you can use the %MDX function, which is an InterSystems extension. This function executes an MDX query, which should return a single value, and returns that value, which is unaffected by the context in which you execute the function. This means that you can calculate percentages with measures defined by value expressions like this:
100 * MEASURES.[measure A] / %MDX("SELECT FROM mycube")
For example:
WITH MEMBER MEASURES.PercentOfAll AS '100 * MEASURES.[%COUNT]/%MDX("SELECT FROM demomdx")'
SELECT MEASURES.PercentOfAll ON 0, diagd.MEMBERS ON 1 FROM demomdx
PercentOfAll
1 None 84.56
2 asthma 6.85
3 CHD 3.18
4 diabetes 4.89
5 osteoporosis 2.21
Distinct Member Count
In some cases, for a given cell, you want to count the number of distinct members of some particular level. For example, the DocD dimension includes the level Doctor. We could count the number of unique doctors who are primary care physicians for any given set of patients. To do so, we define a calculated measure that uses the following value_expression:
COUNT([docd].[h1].[doctor].MEMBERS,EXCLUDEEMPTY)
We can use this measure in a query as follows:
WITH MEMBER MEASURES.[distinct doctor count] AS 'COUNT(docd.doctor.MEMBERS,EXCLUDEEMPTY)'
SELECT MEASURES.[distinct doctor count] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
distinct doctor co
1 0 to 9 38
2 10 to 19 38
3 20 to 29 38
4 30 to 39 40
5 40 to 49 41
6 50 to 59 40
7 60 to 69 33
8 70 to 79 31
9 80+ 28
Semi-Additive Measures
A semi-additive measure is a measure that is aggregated across most but not all dimensions. For example, customers’ bank balances cannot be added across time, because a bank balance is a snapshot in time. To create such measures, you can use the %LAST function, an InterSystems extension to MDX.
Consider the following measures:
-
Balance is based on the source property CurrentBalance and is aggregated by summing.
You would avoid aggregating this measure over time, because it would give incorrect results; that is, you should use this measure only in pivot tables that include a time level for rows or columns.
-
Transactions is based on the source property TxCount and is aggregated by summing.
You can define a calculated measure called LastBalance and use the following value_expression:
%LAST(Date.Day.Members,Measures.Balance)
The %LAST function returns the last non-missing value for a measure evaluated for each member of the given set. In this case, it finds the last day that has a value and returns that value.
Filtered Measures (Tuple Measures)
A normal measure considers all records in the fact table for which the source value is not null. In some cases, you may want to define a filtered measure, which has the following behavior:
-
The measure is null for certain records.
-
For the other records, the measure has a value.
For a filtered measure (also informally called a tuple measure), use a value_expression like the following:
([MEASURES].[my measure],[DIMD].[HIER].[LEVEL].[member name])
In this case, value_expression is a tuple expression where:
-
[MEASURES].[my measure] is the measure to use as a basis.
-
[DIMD].[HIER].[LEVEL].[member name] is the member for which the measure value should be non-null.
For example, the Avg Test Score measure is the average test score considering all patients who have a non-null value for the test. Suppose that in addition to the Avg Test Score measure, your customers would like to see another column that just shows the average test scores for patients with coronary heart disease (the CHD diagnosis). That is, the customers would like to have the measure Avg Test Score - CHD. In this case, you can create a calculated measure that has the following value_expression:
(MEASURES.[avg test score],diagd.h1.diagnoses.chd)
For example:
WITH MEMBER MEASURES.[avg test score - chd] AS
'(MEASURES.[avg test score],diagd.h1.diagnoses.chd)'
SELECT MEASURES.[avg test score - chd] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
avg test score - c
1 0 to 9 *
2 10 to 19 *
3 20 to 29 *
4 30 to 39 *
5 40 to 49 78.00
6 50 to 59 75.75
7 60 to 69 80.71
8 70 to 79 83.33
9 80+ 55.25
Measures for Another Time Period
It is often useful to view the value of a given measure for an earlier time period, while viewing a later time period. As an example, you can define a calculated measure called UnitsSoldPreviousPeriod and use the following value_expression:
([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])
Because of how this measure is defined, it is meaningful only if you use the DateOfSale dimension on the other axis of the query. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold DateOfSale
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
...
Notice that the caption of the second column is based on the dimension used within the value expression, rather than the name of the calculated member that we defined. We can use the %LABEL function to provide a more suitable caption. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],%LABEL([MEASURES].[UNITSSOLDPREVIOUSPERIOD],"Units (Prv Pd)","")} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold Units (Prv Pd)
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
6 Jun-2009 17 22
7 Jul-2009 24 17
8 Aug-2009 30 24
...
These examples use a time-based level, because this kind of analysis is common for time levels. You can, however, use the same technique for data levels.
Measures That Refer to Other Cells
It is often useful to refer to the value in a different cell of the pivot table. To do so, you can use the %CELL and %CELLZERO functions. Each of these functions returns the value of another cell of the pivot table, by position. If the given call has no value, %CELL returns null; in contrast, %CELLZERO returns zero.
These functions have many uses. For one example, you can use %CELL to calculate a running total (in this case, the cumulative inches of rainfall):
SELECT {MEASURES.[Rainfall Inches],%CELL(-1,0)+%CELL(0,-1)} ON 0, {dated.year.1960:1970} ON 1 FROM cityrainfall
Rainfall Inches Expression
1 1960 177.83 177.83
2 1961 173.42 351.25
3 1962 168.11 519.36
4 1963 188.30 707.66
5 1964 167.58 875.24
6 1965 175.23 1,050.47
7 1966 182.50 1,232.97
8 1967 154.44 1,387.41
9 1968 163.97 1,551.38
10 1969 184.84 1,736.22
11 1970 178.31 1,914.53
MDX Recipes for Non-Measure Calculated Members
This section provides recipes for non-measure calculated members for some common scenarios:
Defining Age Members
It is often useful to have members that group records by age. To define such members, use an existing time level and the special NOW member. For example, consider the MonthSold level in the HoleFoods sample. You could define a calculated member named 3 Months Ago with the following value_expression:
[dateofsale].[actual].[monthsold].[now-3]
For example:
WITH MEMBER CalcD.[3 months ago] as '[dateofsale].[actual].[monthsold].[now-3]'
SELECT calcd.[3 months ago] ON 0, {MEASURES.[units sold], MEASURES.target} ON1 FROM holefoods
3 months ago
1 Units Sold 37
2 Target 254.00
Defining a Hardcoded Combination of Members
In many cases, it is useful to define a coarser grouping that combines multiple members of the same level. To do so, create a non-measure calculated member that has a value_expression of the following form:
%OR({member_expression, member_expression,...})
For example:
%OR({colord.red,colord.blue,colord.yellow})
Each non-measure member refers to a set of records. When you create a member that uses the %OR function, you create a new member that refers to all the records that its component members use.
For example:
WITH MEMBER CalcD.[primary colors] as '%OR({colord.red,colord.blue,colord.yellow})'
SELECT calcd.[primary colors] ON 0,
{MEASURES.[%COUNT], MEASURES.[avg test score]} ON 1 FROM demomdx
Defining a Combination of Members Defined by a Term List
Term lists provide a way to customize a Business Intelligence model without programming. A term list is a simple (but extendable) list of key and value pairs. You can use term lists in the multiple ways; one is to build a set of members, typically for use in a filter.
In this case, you use the %TERMLIST function and the %OR function; create a non-measure calculated member that has a value_expression of the following form:
%OR(%TERMLIST(term_list_name))
Where term_list_name is a string that evaluates to the name of a term list.
For example:
%OR(%TERMLIST("My Term List"))
This expression refers to all records that belong to any of the members indicated by the term list (recall that %OR combines the members into a single member).
The %TERMLIST function has an optional second argument; if you specify "EXCLUDE" for this argument, the function returns the set of all members of the level that are not in the term list.
Aggregating Ranges of Dates
Another useful form uses a range of members aggregated by %OR:
%OR(member_expression_1:member_expression_n)
The expression member_expression_1:member_expression_n returns all members from member_expression_1 to member_expression_n, inclusive. This form is particularly useful with time levels, because you can use it to express a range of dates in a compact form.
For time levels, you can also use the special NOW member. The following expression aggregates sales records from 90 days ago through today:
%OR(DateOfSale.DaySold.[NOW-90]:DateOfSale.DaySold.[NOW])
Or use the following equivalent form:
%OR(DateOfSale.DaySold.[NOW-90]:[NOW])
You can also use the PERIODSTODATE function to get a range of dates. For example, the following expression gets the range of days from the start of the current year to today and aggregates these days together:
%OR(PERIODSTODATE(DateOfSale.YearSold,DateOfSale.DaySold.[NOW]))
Defining a Member as an Intersection of Other Members
In some cases, typically when you define a filter, it is useful to define a member that is an intersection of members. Suppose that you need a filter like the following (which does not show literal syntax):
Status = "discharged" and ERvisit = "yes" and PatientClass="infant"
Also suppose that you need to use this filter in many places.
Rather than defining the filter expression repeatedly, you could define and use a calculated member. For this calculated member, specify Expression as follows:
%OR({member_expression,member_expression,...}
For example:
%OR({birthd.year.NOW,allersevd.[003 LIFE-THREATENING]}
The expression (birthd.year.NOW,allersevd.[003 LIFE-THREATENING]) is a tuple expression, which is the intersection of the member birthd.year.NOW and the member allersevd.[003 LIFE-THREATENING] — that is, all patients who were born in the current year and who have a life-threatening allergy.