TOTAL vs Aggr in Qlikview

Question

I'm trying to understand how TOTAL and Aggr work in qlikview. Could someone please explain the difference between the two examples below, and if possible please illustrate with a sql query?

Code:

Example1:

Max({<Field1=>} Aggr(Sum({<Field2={'Value'}, Field1=>} StuffCount), Field1))


Example2:

Max({<Field1=>} TOTAL Aggr(Sum({<Field2={'Value'}, Field1=>} StuffCount), Field1))

Answer 1

Not sure what you mean with and SQL query in this example. Anyway, imagine you have this list of Customers (CustomerID) and Sales (Sales):

CustomerID/ Sales

• Customer1 25
• Customer2 20
• Customer1 10
• Customer1 5
• Customer1 20
• Customer3 30
• Customer2 30

Then you want to show it on a pivot table with dimension CustomerID and two expressions:

1. Max(Aggr(Sum(Sales), CustomerID)) // this will show 60 for the first customer, 50 for the second and 30 for the third one
2. Max(TOTAL Aggr(Sum(Sales),CustomerID)) //this will show 60 in every row of your table (which is the maximum sum of sales among all customers)

So basically AGGR creates a temporal list of whatever you put in the first function input (in this case sum(Sales)) using the dimension of the second (CustomerID). Then you can perform operations on that list (such as Max, Min, Avg...). If you write TOTAL and use the expression in a pivot table, then you 'ignore' the dimensions that might be affecting the operations. Hope it helps

Answer 2

TOTAL keyword is useful in charts/pivot tables. It applies the same calculation on every datapoint in the chart/pivot, with independence of dimentions.

Therefore - if you put your expression into pivot table - 1st option may display different values per cell (if the Aggr is rellevant) when the 2nd will result in same values.

Aggr function allows making double aggregations (avg of sum, max of count etc..) on different group by bases.