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

Last modified: Apr 18, 2023

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment