aggregation help

[mahitham]

Hi Experts,

Can any one please help me on below issue.

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 )

)

Thanks in advance.

[ogautier62]

Hi

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

Because with your formula it adds scope of other names

Regards

[mahitham]

Hi ogautier62,

Thanks for your reply.

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

[mtucholski]

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 )

)