Aggregated Expressions

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.

!These types of Expressions are essential to solve some common issues.
For example, see Pitfall : Calculating ratios, averages, ….

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

Code192 2020 All rights reserved