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 :
- Avg
- Count
- Distinct Count
- Max
- Min
- Sum
- STDev
- STDevP
- Var
- VarP
They are listed in the “Aggregation” category of functions in the Expression Editor.
Rules
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.
Usage
Aggregated Expressions will always be calculated correctly, independently of how data is grouped inside the Widget.
Example
Imagine a simple dataset :
TagName | Building | Product | Price |
---|---|---|---|
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 :
TagName | Building | Product | Price | ExpressionTotal |
---|---|---|---|---|
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 :
Product | ExpressionTotal |
---|---|
Product X | 60 |
Product Y | 75 |
⇒ 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 :
ExpressionTotal |
---|
135 |
⇒ because 135 is the total SUM 10+15+20+25+30+35
Post your comment on this topic.