Database Reference
In-Depth Information
SUM(YTD([Order Date].Calendar.CURRENTMEMBER),
Measures.[Sales Amount])
Moving averages are used to solve very common business problems. They
are well suited to track the behavior of temporal series, such as financial
indicators or stock market data. As these data change very rapidly, moving
averages are used to smooth out the variations and discover general trends.
However, choosing the period over which smoothing is performed is essential,
because if the period is too long, the average will be flat and will not be
useful to discover any trend, whereas a too short period will show too many
peaks and troughs to highlight general trends.
The
LAG
function we have seen in the previous section, combined with
the range operator '
:
', helps us to write moving averages in MDX. The range
operator returns a set of members made of two given members and all the
members in between. Thus, for computing the 3-month moving average of
the number of orders, we can write the following query:
WITH MEMBER Measures.MovAvg3Months AS
AVG([Order Date].Calendar.CURRENTMEMBER.LAG(2):
[Order Date].Calendar.CURRENTMEMBER,
Measures.[Order No]), FORMAT STRING =
'
###,##0.00
'
SELECT
{
Measures.[Order No], Measures.MovAvg3Months
}
ON COLUMNS,
[Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales
WHERE (Measures.MovAvg3Months)
The
AVG
function, like
SUM
, returns the average of an expression evaluated
over a set. The
LAG(2)
function obtains the month preceding the current one
by 2 months. The range operator returns the set containing the 3 months
over which the average of the number of orders is computed. The answer of
this query is given next.
Order No MovAvg3Months
July 1996
21
21.00
August 1996
25
23.00
September 1996
21
22.33
October 1996
25
23.67
November 1996
25
23.67
December 1996
29
26.33
···
···
···
As can be seen, the average for July 1996 is equal to the number of orders, as
there are no prior data, while the average for August 1996 will be computed
from the data from July and August 1996. From September 2006 onward, the
average will be computed from the current month and the prior 2 months.
Search WWH ::
Custom Search