What are Aggregated Expressions
There is a special type of Expressions : aggregated Expressions.
An aggregated Expression is simply an Expression that uses a function from the “Aggregation” category of functions.
Note : Aggregation Functions
Since Alpana v3.0, supported Aggregation Functions are :
- Distinct Count
They are listed in the “Aggregation” category of functions in the Expression Editor.
Note : these rules come from the underlying SQL engine. You will find the same rules in SQL queries.
- Non-aggregated fields cannot be used in the same Expression as aggregated fields. See this chapter.
- Aggregation functions cannot wrap other aggregation functions. See this chapter.
In particular, when binding an aggregated Expression as Measure to a Widget, only the “aggregation” type “Value of” can be used.
Aggregated Expressions will always be calculated correctly, independently of how data is grouped inside the Widget.
Imagine a simple dataset :
|FT101.Value||Building A||Product X||10|
|FT102.Value||Building A||Product Y||15|
|FT201.Value||Building B||Product X||20|
|FT202.Value||Building B||Product Y||25|
|FT301.Value||Building C||Product X||30|
|FT302.Value||Building C||Product Y||35|
You create the following Expression :
ExpressionTotal = SUM(Price)
If you display this full dataset in Grid, you get :
|FT101.Value||Building A||Product X||10||10|
|FT102.Value||Building A||Product Y||15||15|
|FT201.Value||Building B||Product X||20||20|
|FT202.Value||Building B||Product Y||25||25|
|FT301.Value||Building C||Product X||30||30|
|FT302.Value||Building C||Product Y||35||35|
⇒ It seems like the
SUM() part of the Expression didn’t do anything.
Now if you display this same Expression in a Pie Chart grouped by “Product”, you get :
⇒ because 60 is the SUM 10 + 20 + 30 and 75 is the SUM 15 + 25 + 35
Now if you display this same Expression in a Card Widget, grouped across all the dataset, you get :
⇒ because 135 is the total SUM 10+15+20+25+30+35