Total vs AGGR()

[siddharth_kulka]

Hello,

What is the difference between using the use TOTAL keyword and AGGR () Function.

Example:

sum(TOTAL<Month>Sales) will return Monthly Sales

aggr( NODISTINCT sum(Sales), Month ) will also return monthly Sales.   

Question:

1. So in what scenario, is aggr () different than TOTAL. And how can it be used wisely?

2. Also, performance wise, I have found TOTAL to be better, even when the number of records are more. Can some one confirm this!

Any comments/views will be appreciated!

Regards,

Siddharth

[#2]

Total will just enable dynamic grouping by expressions while aggr() creates a temp table within the datamodel. Hence aggr() will have both dimensions and metrics within and occupies more memory. TOTAL on the other hand will just work only the expression without creating the table and consumes less memory.

Where to use:

Total is analogous to SQL group by while Aggr is analogus to SQL Sub Query.

Kiran.

[siddharth_kulka]

Sounds good Kiran...

How do we know that a temp table is created in memory...is any documentation available for the same?

Siddharth

[#4]

I am not sure about documentation but the following example will demonstrate that table is created.

if(Aggr(sum(sales),Month)>=vMonthSalesTarget,Month)

This will return only the months with sales > target. If a table is not created, Month dimension cannot be returned as its not part of the expression.

Kiran.

[siddharth_kulka]

Okay.

But we can also achieve the above using Total

if(sum(total <month> Sales)>vMonthSalesTarget,Month).

So is it fair to say that both can be used interchangibly, but TOTAL is better performance wise becuase of NON temp table creation?

Siddharth

[ToniKautto]

The functions are by definition different, as aggr() returns a set of values while sum() return a single numeric value. The sum function will also aggregate over the chart dimensions while aggr() will aggregate over the specified dimensions, so the function are quite different. This is also the reason for why the temp table needs to be created.

TOTAL qualifier means that the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions. In combination with the <fld, fld> the aggregation will be done on a group by group basis instead of the entore total, which can visually create the same result as an aggr().

[#7]

Please check QlikView Reference Manual.pdf Chapter 22.NESTED AGGREGATIONS AND RELATED ISSUES

Hope this helps.

Best Regards,
Vishal

[#8]

Hi Siddharth,

I just tested the your expression in list box and it didnt work. While the aggr worked. I can add one more difference here, if you are using total in a expression (say within a straight table), the dimensions on total should be in the table for the expression to work. So total works after the table in the memory is created.

Regards,

Kiran.

[ToniKautto]

That is correct Kiran. Just as I said before the sum() aggregates over the chart dimensions, so it will not work in a list box without diemensions.