Databases Reference
In-Depth Information
Using the TOTAL qualifier
The
TOTAL
qualifier is added to aggregation functions to disregard chart dimensions
and make the calculation over the entire record set as defined by the current
selection state.
In a previous section, we calculated the percentage of FTEs each carrier performs
relative to the total number of FTEs and we used a variable (
vTotalFTEs
) to store
the divisor value. Instead of using a variable, the same calculation can be made
using the following expression:
Sum([# Equivalent FTEs]) / Sum(TOTAL [# Equivalent FTEs])
When the preceding expression is used in a straight table with
Carrier Name
as the
dimension, the numerator will calculate the value corresponding to each carrier,
which will be different on each row of the table. The divisor will calculate the total
number of flights made by all carriers, which will be the same for all rows of the
table, therefore disregarding the dimension value.
Additional modifiers can be used along with the
TOTAL
qualifier to disregard only
some of the dimensions in a chart, instead of all of them; that is, in cases where there
is more than one dimension in a chart.
For instance, if the straight table described earlier, besides the
Carrier Name
dimension, also has the
Year
and
Month
dimensions, we can add any of those
dimensions to a list of fields enclosed in angular brackets, and separated by a
comma, to specify which of them should the
TOTAL
qualifier disregard.
Let's take the already created pivot table in the
Reports
tab of our
Airline
Operations
document and add three new expressions to it to better illustrate how the
TOTAL
qualifier works. The three expressions we'll add, along with their labels, are:
• Participation to the whole:
Sum([# Departures Performed]) / Sum(TOTAL [# Departures
Performed])
This expression will return the percentage of flights for a particular carrier,
month, and year relative to the total flights of all carriers, and for all years
and months available in the current selection state.
