Databases Reference
In-Depth Information
The corresponding set modifiers to obtain the compare-to periods are:
• Previous Year Year-To-Date
<PeriodID = {"<=$(=Max(PeriodID)-12)"}, Year = {"$(=Max(Year) -
1)"}, Quarter = , Month = , Period = >
This set modifier subtracts 12 from the current period's ID field to obtain the
corresponding period from last year. It also subtracts 1 from the
Year
value
to obtain the previous year. As we are now using the new
PeriodID
field, the
old
Period
field is specified as an ignored-selections field.
• Same month last year
<PeriodID = {"$(=Max(PeriodID)-12)"}, Year = , Quarter = , Month =
, Period = >
By subtracting 12 from the current period's ID, we obtain the corresponding
month from last year.
• Previous month
<PeriodID = {"$(=Max(PeriodID)-1)"}, Year = , Quarter = , Month =
, Period = >
By subtracting 1 from the current period's ID, we obtain the previous month's
record set.
Now that we have defined our set modifiers, it's time to construct the expressions.
Constructing the expressions
Using the base and compare-to set modifiers that we just defined, our final
expressions will be as described here.
The following examples use the
# Departures
Performed
field in the aggregation function, but it
can be changed to obtain any other indicator.
The following expressions are constructed in the following form to obtain a
variance percentage:
(BasePeriod / CompareToPeriod) - 1
• Current Year-To-Date versus the same period last year
(Sum({$<PeriodID = {"<=$(=Max(PeriodID))"}, Year =
{"$(=Max(Year))"}, Quarter = , Month = , Period = > } [#
Departures Performed])
/
