Show TOC

Examples of Using the CMR OperatorLocate this document in the navigation structure

If your queries incorporate key fiigures restricted by CURRENT MEMBER variables, you can define formulas using the CMR (CURRENT MEMBER result) operator. This data function does not calculate the results based on the data in the InfoProvider. Instead it calculates the them based on the values moved by the CURRENT MEMBER variable.

The folowing examples will illustrate how the CMR operator works.

Example Query 1

In the following example, the variable with replacement path CURRENT MEMBER refers to the content time characteristic Month (0CALMONTH). CMR in the header refers to the CURRENT MEMBER result operator. Sales in December 2006 = 2.00.

Table 1: Example query 1 with CURRENT MEMBER variables for the month
Calendar Year/Month Sales CM-1 - CM AVG (CM-1 - CM, Month) AVG (CMR (CM-1 - CM), Month)
01.2007 2,00 2,00 2,00
02.2007 20,00 20,00 20,00 20,00
03.2007 7,00 27,00 13,50 27,00
04.2007 7,00 7,00 7,00
05.2007 6,00 6,00 6,00 6,00
06.2007 10,00 16,00 8,00 16,00
07.2007 1,00 11,00 5,50 11,00
08.2007 1,00 1,00 1,00
09.2007
10.2007 6,00 6,00 6,00 6,00
11.2007 27,00 33,00 16,50 33,00
12.2007 3,00 30,00 15,00 30,00
Result 80,00 82,00 9,11 14,455

The query contains the following key figures:

  • Sales: This column shows the Net Sales figure per month. Sales is a key figure in the InfoProvider.

    • Result: Total of all sales figures posted in 2007.

  • (CM-1) - CM, in other words (CURRENT MEMBER (0CALMONTH)-1) - CURRENT MEMBER (0CALMONTH): This is rolling window. The system calculates the cumulative sales from the last three months.

    • The value for month 07.2007 is 11.00, as (CM-1) - CM defines the interval of the months 06.2007 (= CM-1) to 07.2007, and the total for the months of June (10,00) and July (1,00) is exactly 11.00.

    • Result: Total sales figure from 2006 to December 2007.

  • AVG ((CM-1) - CM, Monat), in other words AVG ((CURRENT MEMBER (0CALMONTH) - 1) bis CURRENT MEMBER (0CALMONTH)): This key figure is defined using formula exception aggregation AVG for reference characteristic 0CALMONTH. The system calculates the average sales per month based on incoming data from the last two months, i.e. the two-month sales average.

    • The value for month 07.2007 is 5.50, as the system divides the cumulative sales for the months of June to July - 11.00 - by the number of months with incoming data, in this case 2.

    • The value for month 08.2007 is 1.00, as the system divides the cumulative sales for the months of July to August - 1.00 - by the number of months with incoming data, in this case 1.

    • Result: Average sales per month. The system calculates the total sales for all months (82.00) and divides this by the number of months with incoming postings (1 in 2006 and 8 in 2007 - divided by 9).

    Note

    An example of where average calculations of this kind are used is the 200 day average of sales.

  • AVG (CMR (CM-1 - CM), Monat), in other words CMR ((CURRENT MEMBER (0CALMONTH) - 1) bis CURRENT MEMBER (0CALMONTH)): This key figure shows the average of the interval values and not the average of the incoming values. This is specified by the CMR operator. This means that the average is not calculate based on the data in the InfoProvider. Instead, it is calculated based on the values moved by the CURRENT MEMBER variable (see (CM-1) - CM)). The values from the previous month are included in the range for the current month, meaning that it is not possible to differentiate for individual months between the cumulative values in columns (CM-1) - CM) and values changed by the CMR operator in AVG (CMR (CM-1 - CM), Monat). The difference can be seen in the result however.

    • Result: Average sales per interval. The system calculates the total sales from all intervals (159) and divides this by the number of intervals with values (11 in 2007).

Example Query 2

This example shows a query that is used to find out how many months show growth in sales compared to the previous month.

Table 2: Example Query 2
Calendar Year/Month Sales for Current Month Sales for Previous Month Difference in Sales to Previous Month Difference in Sales to Previous Month > 0
01.2007 2,00 -2,00 0
02.2007 20,00 20,00 1
03.2007 7,00 20,00 -13,00 0
04.2007 7,00 -7,00 0
05.2007 6,00 6,00 1
06.2007 10,00 6,00 4,00 1
07.2007 1,00 10,00 -9,00 0
08.2007 1,00 -1,00 0
09.2007 0
10.2007 6,00 6,00 1
11.2007 27,00 6,00 21,00 1
12.2007 3,00 27,00 -24,00 0
Result 80,00 79,00 1,00 5,00

The query contains the following key figures:

  • Sales for Current Month: This column shows the Net Sales figure per month. Sales is a key figure in the InfoProvider.

    • Result: Total of all sales figures posted in 2007.

  • Sales for Previous Month, in other words Umsatz von (CURRENT MEMBER (0CALMONTH)-1): This column shows the Net Sales figure from the previous month.

    • Result: Total sales figure from December 2006 to end of November 2007.

  • Difference in Sales to Previous Month, in other words Umsatz (CM) - Umsatz (CM-1): The system subtracts the sales for the previous month from the sales for the current month.

    • The value for month 07.2007 is -9,00, as the system subtracts the sales for month 06.2007- 10.00 - from the sales for month 07.2007- 1.00.

    • Result: Difference between results: 80.00 - 79.00 = 1.00.

  • Differenz des Umsatzes zum Vormonat > 0, in other words CMR (Umsatz (CM) - Umsatz (CM-1)): This key figure is defined using the CMR operator. If the condition is met, the system returns 1. Otherwise it returns 0.

    • Result: Total of all positive values, in this case 5.00. 5 months displayed growth in sales when compared to the previous month.