What is the difference between using the use TOTAL keyword and AGGR () Function.
sum(TOTAL<Month>Sales) will return Monthly Sales
aggr( NODISTINCT sum(Sales), Month ) will also return monthly Sales.
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!
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.
I am not sure about documentation but the following example will demonstrate that table is created.
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.
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?
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().
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.