Dynamic Aggr() using Group Dimension

[#1]

Hi:

Is it possible to use a cycle group dimension in Aggr() function?

I am thinking something like : Avg(Aggr(Sum(fSettlement),GetCurrentField(CycleGroup))).

But it doesn't work.

Do I need to add a dollar sign somewhere?

Thank you very much.

PC

[Anonymous]

Aggr(Sum(fSettlement),GetCurrentField(CycleGroup)) will work but

Avg(Aggr(Sum(fSettlement),GetCurrentField(CycleGroup))).will not work

[#3]

Hi Nitin:

Thank you very much for your answer.

Is there any reason why I cannot take the average?

PC

[Anonymous]

This is because aggregate functions are not allowed in dimensions without AGGR().

[#5]

Hi Nitin:

What if I want to calculate the average settlements by a field in the cycle group, say consultants.

(I use a settlement flag which takes the value of 1 or 0)

What should I do?

Thanks

PC

[Anonymous]

Hi,

Checkout  RangeAvg() function. This will help you to sort out your query.

Thanks

[jonathandienst]

Hi

I think you can do something like this:

     =Avg(Aggr(Sum(fSettlement), $(=GetCurrentField(CycleGroup))))

HTH

Jonathan

[jonathandienst]

Here is an example

[#9]

Thank you everyone.

I found the solution in the Cookbook.

You need to add "Square Bracket" around the whole dollar sign thing. So it would look like this.

=Avg(Aggr(Sum(fSettlement), [$(=GetCurrentField(CycleGroup))]))

It will have a red line underneath but still saying "Expression OK". Not sure about this.

Thank you.

PC

[canmarroig]

Useful!! Tks