Database Reference
In-Depth Information
The formula is correct in this case, but in order to make it work properly for any
measure, we should use the following:
CREATE MEMBER CURRENTCUBE.Measures.[Sales Amount YTD] AS
AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount]);
Here we replaced the MDX
SUM
function with
AGGREGATE
. The result for
Sales
Amount
is the same. However, if we need to use this formula on a measure that does
not have its
AggegationFunction
property set to
Sum
,
AGGREGATE
will aggregate
values differently for each different
AggegationFunction
, whereas
SUM
will always
provide the sum. For example, consider a measure that has its
AggegationFunction
property set to
DistinctCount
: if we had 5 distinct customers on 1
st
January, 3 distinct
customers on 2
nd
January, and 4 distinct customers on 3
rd
January, the total number of
distinct customers from the 1
st
to the 3
rd
of January would not be 5+3+4=12 because
some customers might have sales on more than one day. Using
AGGREGATE
in this case
will provide the correct number of distinct customers in the year-to-date.
Ratios over a hierarchy
Another very common pattern of calculated member is the ratio. A good example of
this is when we want to compute the ratio of a measure (for example, sales amount)
on a member of a hierarchy against its parent.
Consider, for example, the
Customer Geography
hierarchy in the
Customer
dimension. We might want to compute two different kinds of ratios:
•
Ratio over Country
: This is the ratio of the selected member against the
whole of the country. This gives a percentage that shows how much a State
or City's sales contribute to the sales of the whole country.
•
Ratio over Parent
: This is the ratio of the selected member against its parent
in the hierarchy rather than the whole country. So, for example, we would
see the percentage contribution of a City to the Region it was in, if City was
the level immediately underneath Region in the hierarchy.
Search WWH ::
Custom Search