Databases Reference
In-Depth Information
Deining the set modifiers
We'll start tackling each requirement by first defining the set modifiers each
period comparison would need. For that, we must remember that the current
time-related fields in which the user is able to make a selection are
Year
,
Quarter
,
Month
, and
Period
.
However, not always will the user have explicitly selected values in all fields. There
can be selections in
Year
only, for instance, or
Year
and
Month
, or only
Month
, or
even no selection whatsoever.
The set modifiers we define when building our expressions need to account for all of
the possible scenarios and always show a clear result. This will ensure the user is not
confused as to what filters are being applied when navigating the document.
Even if the user has not made specific selections on all time-based fields, we can
easily infer a period in which to base our comparisons by taking the most-recent
period from the list of associated values. Let's suppose the user has the following
selection state:
• Year =
2010
• Quarter =
Q2
From the previous example, we can infer that the "current month" (our base period)
is June 2010, as it is the most recent period in the list of possible values.
If, on the other hand, the user has nothing selected at all, we will take December 2011
as our base period, as it is the latest month available in the dataset.
Obtaining the base period record set
A simple
Max(Period)
expression will help us get the base period in all scenarios,
as the
Period
field contains both the
Month
and
Year
components. Therefore, the set
modifiers that we would use to get the record set corresponding to the base period in
each of the required comparisons are:
• Current Year-To-Date indicators:
<Period = {"<=$(=Max(Period))"}, Year = {"$(=Max(Year))"}, Quarter
= , Month = >
This set modifier will result in a record set containing all of the periods that
are less than or equal to the current period, and belong to the current year,
which would be all Year-To-Date records.
