3 Replies Latest reply: Aug 10, 2018 3:24 AM by MATEUSZ TUCHOLSKI

# aggregation help

Hi Experts,

I have calculated CC1% and CC2% with below formulas.

Here Each Name contains multiple SCOPE values. by default CC1%   and CC2% are showing wrong values but when I have filtered one particular Name =aim1 getting correct percentage values.

Please find the below attached image. Please help me to get the related percentage values of aim1 and aim2 without filtering.

Please find the attached sample app and sample excel with calculations in the second sheet.

CC1% :

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

CC2%:

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

• ###### Re: aggregation help

Hi

May be try sum(total <date, scope, name>

Because with your formula it adds scope of other names

Regards

• ###### Re: aggregation help

Hi ogautier62,

When I have applied sum(total <date, scope, name> in the CC1% the percentage values are reducing.

• ###### Re: aggregation help

Hi,

there is something wrong with denomiator. Have you tried this?

CC1% :

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL <Name>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

CC2%:

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL <Name>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)