Skip to main content
Previous section   

Creating Calculated Measures and Members

This chapter describes how to create and use calculated measures and members. It discusses the following topics:

Note:

Your cubes might contain additional calculated members that you can use in all queries; see Defining DeepSee Models.

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 book 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
Copy code to clipboard
Tip:

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'
    Copy code to clipboard

    Later sections of this chapter 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
Copy code to clipboard
Note:

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 DeepSee 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
Copy code to clipboard

Or:

(MEASURES.[measure A] + MEASURES.[measure B])/MEASURES.[measure C]
Copy code to clipboard

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. DeepSee 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, DeepSee supports several scalar functions: SQRT, LOG, and POWER.

Tip:

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")
Copy code to clipboard

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
Copy code to clipboard

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)
Copy code to clipboard

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
Copy code to clipboard

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)
Copy code to clipboard

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])
Copy code to clipboard

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)
Copy code to clipboard

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
Copy code to clipboard

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])
Copy code to clipboard

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
...
Copy code to clipboard

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
...
Copy code to clipboard

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
Copy code to clipboard

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]
Copy code to clipboard

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
Copy code to clipboard

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,...})
Copy code to clipboard

For example:

%OR({colord.red,colord.blue,colord.yellow})
Copy code to clipboard

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
Copy code to clipboard

Defining a Combination of Members Defined by a Term List

Term lists provide a way to customize a DeepSee 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))
Copy code to clipboard

Where term_list_name is a string that evaluates to the name of a term list.

For example:

%OR(%TERMLIST("My Term List"))
Copy code to clipboard

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)
Copy code to clipboard

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])
Copy code to clipboard

Or use the following equivalent form:

%OR(DateOfSale.DaySold.[NOW-90]:[NOW])
Copy code to clipboard

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]))
Copy code to clipboard

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"
Copy code to clipboard

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,...}
Copy code to clipboard

For example:

%OR({birthd.year.NOW,allersevd.[003 LIFE-THREATENING]}
Copy code to clipboard

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.