11 Replies Latest reply: Feb 24, 2017 6:44 AM by Rajesh RS

# Nested Aggregation Function

Hello All,

I want the correct expression containing the AGGR Function for the given query.

Tried a few things but it didn't work out.

I'm posting the example below.

Sum[Count( Distinct (mobilecode))/Count(Distinct (routecode))]

Where mobilecode and routecode are field names.

• ###### Re: Nested Aggregation Function

Hi Chanin,

Sum(Aggr((Sum([mobilecode])),[routecode]))

Regards,

Rajesh R. S.

• ###### Re: Nested Aggregation Function

Hi,

Assuming that you are counting the number of mobile codes in given routecode.

Below is the AGGR function.

=Aggr(count(DISTINCT mobilecode),routecode)

• ###### Re: Nested Aggregation Function

SUM(Aggr(COUNT(Distinct mobilecode)/COUNT(Distinct routecode),YourDimensionField1,YourDimensionField2))

or

Avg(Aggr(COUNT(Distinct mobilecode)/COUNT(Distinct routecode),YourDimensionField1,YourDimensionField2))

• ###### Re: Nested Aggregation Function

What to add in the dimension fields . I'm a bit confused in that ??

• ###### Re: Nested Aggregation Function

Where are you using this expression? In chart or straight table?

You need to use those Dimensions here !

• ###### Re: Nested Aggregation Function

So can the answer be found out without using the AGGR function? If yes could you please post the answer .

• ###### Re: Nested Aggregation Function

It's upto where exactly you are using it !

Where you are using this expression?

If possible, provide sample data.

• ###### Re: Nested Aggregation Function

Normally an expression like this would be the right one:

Sum( Aggr( Count(DISTINCT mobilecode) / Count(DISTINCT routecode) , <DIM1> [, <DIM2 , ... ]) )

<DIM1> is a field that is the first grouping level.

[<DIM2>, ....] is an optional list of one or more grouping levels you need or want.

Whether this will work in the context you intend to use it is hard for me to say.

The thing that Aggr introduce is that you aggregate over one or more dimension....

So it is a grouping level you have to select for the Aggr function to work. The result of an Aggr might be 0 or more values. That's why you have to do an aggregation afterwards to be sure that you can display or present the results of the Aggr.

• ###### Re: Nested Aggregation Function

so can this be answered without using the AGGR function ? If yes could you please post the answer .

• ###### Re: Nested Aggregation Function

As I mentioned the answer depends on the context. What do you want to show and where do you want to show it?

In a KPI a single number or in a table with multiple rows?

Yes you can in certain situations get the calculation without using Aggr. You can do it if the inner aggregation use the TOTAL qualifier alone.

Gives us some sample data and an explanation on what you want to see ....

• ###### Re: Nested Aggregation Function

Try:

Sum(aggr(Count( Distinct (mobilecode))/Count(Distinct (routecode)), Dim1, Dim2))

whereby Dim1, Dim2 are the dimensions in your chart respectively those dimensions against the calculation should be run.

- Marcus