2

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?

Example1:

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

Example2:

Max({<Field1=>} TOTAL Aggr(Sum({<Field2={'Value'}, Field1=>} StuffCount), Field1))
pbarney
  • 2,529
  • 4
  • 35
  • 49
user3476463
  • 3,967
  • 22
  • 57
  • 117

2 Answers2

2

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

Carlos Borau
  • 1,433
  • 1
  • 24
  • 34
  • I created a chart with both derived field examples listed above in the description. I'm not getting the max value from example1 as the value of example2. In your example that would the value of 60. Instead in my chart the example2 value is twice the largest value in the example1 field. – user3476463 Aug 01 '17 at 18:36
  • @user3476463 in SQL usually you achieve aggregation with Group by clause, and for for the Total similarity with max(), have a look at this https://stackoverflow.com/questions/3491329/group-by-with-maxdate and this also will help : https://www.techonthenet.com/sql/group_by.php – Carmine Tambascia Aug 31 '17 at 09:13
1

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.

Dimgold
  • 2,748
  • 5
  • 26
  • 49