What is the difference between AGGR and TOTAL function? I have run some tests and can't tell the difference

[jblomqvist]

Hello,

I am trying to understand the difference between TOTAL function and AGGR.

In the attached app I have run numerous tests by breaking down how it works.

I appear to get the same results using TOTAL and AGGR when I add a dimension to the TOTAL field.

Do they do the same thing?

I understand from reading some other threads here that TOTAL works faster than AGGR but other than that I cannot tell the difference.

Can anyone please help explain more?

[marcus_sommer · Accepted solution]

Total isn't a function it's an extension for the expression to ignore all or <Certain Dimensions>. In opposite creates aggr() a virtual table and the expressions will be then applied against these table, see also: AGGR... and of course there could be big performance differences therefore read When should the Aggr() function NOT be used?

- Marcus

[jblomqvist]

Hi Marcus,

So TOTAL works faster than AGGR is the difference?

In my example both would produce exactly the same results?

Aggr seems to be like doing a sub-select in the SQL world where I might want to aggregate using a different dimension to the one in the chart.

[petter]

Aggr is a function than can be used to create advanced aggregations that must include aggregation functions as expresssions and allows you to nest these aggregations - hence the name advance aggregation function. Aggr in itself is not an aggregation but it will need an aggregation function to be used.

Total is a qualifier that prefixes the Set Expression and the Field within an aggregation function. It is not a function in itself.

Aggr can be used to achieve the same effect as using the Total qualifier as you have experienced. But in these cases one should not use the Aggr-function but the Total qualifier. Aggr has uses that are impossible to get by using the Total qualifier in a "normal" aggregation function. Specifically this is the nested aggregations.

[marcus_sommer]

If both expressions return the same value you shouldn't use the aggr() - see the posting from above.

- Marcus