topic Re: Re: Dynamic Aggr() using Group Dimension in QlikView

Dynamic Aggr() using Group Dimension

Mon, 02 Jun 2014 06:24:07 GMT

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.

Re: Dynamic Aggr() using Group Dimension

Anonymous — Mon, 02 Jun 2014 06:39:23 GMT

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

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

Re: Dynamic Aggr() using Group Dimension

Mon, 02 Jun 2014 06:55:00 GMT

Hi Nitin:

Thank you very much for your answer.

Is there any reason why I cannot take the average?

Re: Dynamic Aggr() using Group Dimension

Anonymous — Mon, 02 Jun 2014 07:00:48 GMT

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

Re: Dynamic Aggr() using Group Dimension

Mon, 02 Jun 2014 07:09:38 GMT

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

Re: Dynamic Aggr() using Group Dimension

Anonymous — Mon, 02 Jun 2014 07:21:30 GMT

Hi,

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

Thanks

Re: Dynamic Aggr() using Group Dimension

jonathandienst — Mon, 02 Jun 2014 07:27:23 GMT

I think you can do something like this:

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

HTH

Jonathan

Re: Re: Dynamic Aggr() using Group Dimension

jonathandienst — Mon, 02 Jun 2014 07:33:00 GMT

Here is an example

Re: Dynamic Aggr() using Group Dimension

Mon, 02 Jun 2014 07:40:17 GMT

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.

Re: Dynamic Aggr() using Group Dimension

canmarroig — Fri, 17 Mar 2017 13:22:34 GMT

Useful!! Tks