Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharth_kulka
Creator II
Creator II

Total vs AGGR()

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

8 Replies
Not applicable

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
Creator II
Creator II
Author

Sounds good Kiran...

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

Siddharth

Not applicable

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
Creator II
Creator II
Author

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
Employee
Employee

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().

Not applicable

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

Hope this helps.

Best Regards,
Vishal

Not applicable

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
Employee
Employee

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.