Databases Reference
In-Depth Information
Taking this a step further, we can ask a different, but in a way similar, question: If
we were to classify carriers by the number of interstate routes they serve, how many
carriers would fall under each category?
We could display a straight table with the number of
Interstate Routes
as the
dimension and the number of carriers that fall under each "category" as the
expression. The problem is that we don't have a "number of interstate routes" field
in our data model, nor can we add it as a calculated field in the script because the
calculation varies with each user selection; having a pre-aggregated field is simply
not the answer.
What we can do is perform a nested aggregation that dynamically constructs the
chart's dimension. To do that, follow these steps:
1.
Create a new sheet to allocate the examples in this section; name it
Advanced Expressions
.
2.
Then, click on the
New Chart
button from the design toolbar.
3.
From the initial dialog window, select
Straight Table
in the
Chart Type
section and set the
Title
field to
Carrier Classification by # of
Interstate Routes
.
4.
Click on
Next
and, from the
Dimensions
dialog window, click on the
Add Calculated Dimension…
button.
5.
The
Edit Expression
window will pop up and there we will enter the
dimension's definition, based on the
Aggr
function. Type the following
expression and click OK:
Aggr(Count(DISTINCT [From - To State Code]), [Carrier Name])
This expression will result in a list of values corresponding to the different
number of interstate routes each carrier serves, which is basically the
expression column in the straight table shown in the previous screenshot.
That list will now be our chart's dimension.
6.
From the
Dimensions
window, highlight the calculated dimension we just
created from the
Used Dimensions
list and, in the
Label
field below, type
Interstate Routes
.
7.
Then, click on
Next
to move on to the
Expressions
dialog window. On the
Edit Expression
window, enter the following and click on
OK
:
Count(DISTINCT [Carrier Name])
This expression will count all different carriers so that the final chart shows
the number of carriers each interstate-routes classification has.
