The LastPeriods() Function
The LastPeriods() function, according to the Analysis
Services Books Online, "returns a set of members prior to and
including a specified member." Almost as simple as it sounds, the
function returns (in its typical use within the context of a time dimension)
the set of periods beginning with the period that follows (or lags) the
specified member by a specified index value, less one, up to and including the
specified member. (If the member is not specified in the function, Time.CurrentMember
is defaulted.) Indeed, LastPeriods() resembles PeriodsToDate()(explored
in MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions), which
returns all of the members back to the beginning of a specified period, although
the LastPeriods() function returns the number of members that is
specified by an index value.
The index (minus one) that is specified tells the
function how far to "look back" for purposes of the returned dataset.
If the index is positive, the function returns the set of members whose
range is headed up by the member "lagging" one less than the index
value from the specified member, and that ends with the member itself. If
the index is negative, then LastPeriods() returns the set of
members that begins with the specified member itself, and ends with the member leading
(or "ahead in time") by the negative index value, minus one,
from the specified member. Finally, an empty set is returned if the index
value is zero.
Discussion
The LastPeriods() function allows us to meet numerous
common business needs, including (and especially) those that require that we
return (individually or for accumulation) values from, for example, "several
periods back," up to, and including, the specified period. That
specified member might be, for example, a specific quarter for which we would
like monthly values over the range of, say, two quarters back through the
current quarter. A calculation can be driven for a several month's activities,
as another example, whose range is determined by the beginning and ending
points that result from the index we provide.
As we
discussed in our last lesson, balances are maintained up to the current point
in time / held at various points in time for many accounts whose activity we
might wish to analyze, such as Sales, Inventory, Purchases, and various other
accounts that contain values besides dollar amounts, such as shares
outstanding, headcount, various quantities, and so forth, in our systems. The values most likely to be of interest in typical uses of
the LastPeriods() function would most likely relate to deltas
over the given periods. Ss an illustration, the total sales over each of a
given group of months (itself the delta of "ending total sales" between
the point in time that we establish as the specified member within the
function, and the total sales value that existed, in this example, at the
earlier point in the year to which we are looking back, and which is index -
1 months back). LastPeriods() is excellent for the derivation of "total
activity for the last (whatever number) periods back," and, thus for a "to
date" cumulative total of sorts. Utilitarian beauty appears, again, in
the use of the function with .currentmember, etc. to gain a context
sensitive calculation that can flex automatically as time marches on. Running
averages and other derivatives of the accumulated totals are obvious easy
byproducts, as well, given formulation within an expression that includes LastPeriods().
Common
examples of scenarios where LastPeriods() might come in handy include
monthly totals / balances, over a few quarters, for the inventories of a group
of products we manufacture and sell, for perhaps comparison to the same amounts
for another group of our products, as an aid in determining whether to
discontinue production of a given item or items to allow us to compose a more
rapidly moving product mix. Another illustration might lie in the need to
analyze activity, particularly over several summer months, in total monthly quantity
on hand, for a part that we stock in a Repair Parts account for use in
maintaining our air conditioning plant, to ascertain, perhaps, increased
demands for parts by an aging system.
MDX
provides an excellent means for accumulating these time-ranged activity values
over specified time frames. The LastPeriods() function affords us a
ready means to navigate to the first member of the range for which one endpoint
(the specified member) is defined and for which the opposite end of the range
is specified by the index.
Let's look at an illustration to further clarify the
operation of LastPeriods().
Syntax
Syntactically, the index
and the specified member are placed within the parentheses to the right
of LastPeriods(), as shown in the following illustration:
LastPeriods(<<Index>>[, <<Member>>])
The function returns the set of members
prior to (by <<Index>> - 1 "member-places"),
and including, <<Member>>. The following
simple example expression:
LastPeriods (11, [Time].[1998].[Q3].[8])
would return the following set of
month-members:
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]
[Time].[1998].[Q1].[1]
[Time].[1998].[Q1].[2]
[Time].[1998].[Q1].[3]
[Time].[1998].[Q2].[4]
[Time].[1998].[Q2].[5]
[Time].[1998].[Q2].[6]
[Time].[1998].[Q3].[7]
[Time].[1998].[Q3].[8]}
Practice
Let's reinforce our understanding of the basics we have
covered so far, by using the LastPeriods() function in a manner that
illustrates its operation. The MDX Sample Application (see the second
article in our series, Structure
of the MDX Data Model, for more information about the
Sample Application) will, again, be our tool for constructing and
executing the MDX we examine, and for viewing the result datasets we obtain.
1.
Start the MDX
Sample Application.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the Warehouse
cube in the Cube drop-down list box.
We will compose a simple query to gain an understanding of
our data; our query will focus on the Warehouse Cost measure, a value
that is captured monthly within the FoodMart organization and which is stored
in the Warehouse cube.
5.
Type the following query into
the Query pane:
-- MDX11-1: Tutorial
Query No. 1
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
LastPeriods(11,
[Time].[1998].[Q3].[8]) ON ROWS
FROM Warehouse
6.
Click the Run
button on the toolbar atop the Sample Application, to execute the
query.
Analysis
Services fills the Results pane, presenting the dataset depicted in Illustration
1.
Illustration 1: Result Dataset - LastPeriods() Function
We see
the total organization Warehouse Cost returned for each of the last
eleven periods (our index of 11 in the query), within the range of
periods that ends at August (Quarter 3), 1998, and begins ten (index of eleven
minus one) months back at October (Quarter 4), 1997. As we learned in the
Syntax section above, the result dataset is equivalent to
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]
[Time].[1998].[Q1].[1]
[Time].[1998].[Q1].[2]
[Time].[1998].[Q1].[3]
[Time].[1998].[Q2].[4]
[Time].[1998].[Q2].[5]
[Time].[1998].[Q2].[6]
[Time].[1998].[Q3].[7]
[Time].[1998].[Q3].[8]}
We can
easily prove that the two are equivalent by taking the following steps:
7.
Type the following query into
the Query pane:
-- MDX11-1 Proof: Proof of Query No. 1
SELECT
{[Measures].[Warehouse Cost]} ON COLUMNS,
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12],
[Time].[1998].[Q1].[1],
[Time].[1998].[Q1].[2],
[Time].[1998].[Q1].[3],
[Time].[1998].[Q2].[4],
[Time].[1998].[Q2].[5],
[Time].[1998].[Q2].[6],
[Time].[1998].[Q3].[7],
[Time].[1998].[Q3].[8]}
ON ROWS
FROM Warehouse
8.
Click the Run
button on the toolbar atop the Sample Application, to execute the
query.
As
soon as the specified cells in the Results pane are filled by Analysis
Services, we see the result dataset depicted in Illustration 2.
Illustration 2: The Query Result Dataset
The proof
query delivers the results that we expected, based upon our discussion of the
syntax of the LastPeriods() function: We see that the results are
identical to those we obtained in the previous query, within which we used the
more compact LastPeriods() function.
If it
is desirable to save this or subsequent queries, we can do so by selecting File
-> Save As, and give the file a
meaningful name and location.
Now
let's take a look at our remaining time series function, ParallelPeriod().
» 
Comment and Contribute
